{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pandas 进阶修炼 ｜早起Python\n",
    "<br>\n",
    "\n",
    "**本习题由公众号【早起Python & 可视化图鉴】 原创，转载及其他形式合作请与我们联系（微信号`sshs321`)，未经授权严禁搬运及二次创作，侵权必究！**\n",
    "\n",
    "\n",
    "\n",
    "本习题基于 `pandas` 版本 `1.1.3`，所有内容应当在 `Jupyter Notebook` 中执行以获得最佳效果。\n",
    "\n",
    "\n",
    "不同版本之间写法可能会有少许不同，如若碰到此情况，你应该学会如何自行检索解决。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7 - 数据透视与合并\n",
    "\n",
    "\n",
    "\n",
    "<br>\n",
    "\n",
    "**<font color = '#5172F0'><font size=3.5>必读👇👇👇**</font>\n",
    "    \n",
    "现在让我们继续练习 pandas数据分析另一组常用操作 --> **数据透视与合并**\n",
    "\n",
    "\n",
    "本节习题将涉及四大函数：\n",
    "    \n",
    "- pivot_table\n",
    "- concat\n",
    "- merge\n",
    "- join\n",
    "\n",
    "随着练习的深入，若没有一定的基础知识将很难继续刷题\n",
    "    \n",
    "官方文档永远是最好的学习手册，在本节之前强烈建议学习[官方文档对应部分](https://pandas.pydata.org/pandas-docs/version/0.20/merging.html#database-style-dataframe-joining-merging)\n",
    " \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 初始化\n",
    "\n",
    "<br>\n",
    "\n",
    "该 `Notebook` 版本为**纯习题版**\n",
    "\n",
    "如果需要答案或者提示，可以微信搜索公众号「早起Python」获取！"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据透视表\n",
    "\n",
    "![](http://liuzaoqi.oss-cn-beijing.aliyuncs.com/2021/09/14/16316101294678.jpg?域名/sample.jpg?x-oss-process=style/stylename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1 - 加载数据\n",
    "\n",
    "读取当前目录下 `\"某超市销售数据.csv\"` 并设置千分位符号为 `,`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df=pd.read_csv('某超市销售数据.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2 - 数据透视｜默认\n",
    "\n",
    "制作各省「平均销售额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "销售额",
         "rawType": "float64",
         "type": "float"
        }
       ],
       "ref": "3500825a-df94-4dfc-bf21-112aea72ed8c",
       "rows": [
        [
         "上海",
         "1875.71"
        ],
        [
         "云南",
         "1863.79"
        ],
        [
         "内蒙古",
         "1314.83"
        ],
        [
         "北京",
         "1726.86"
        ],
        [
         "吉林",
         "1701.56"
        ],
        [
         "四川",
         "1157.88"
        ],
        [
         "天津",
         "1611.21"
        ],
        [
         "宁夏",
         "1651.68"
        ],
        [
         "安徽",
         "1477.87"
        ],
        [
         "山东",
         "1642.66"
        ],
        [
         "山西",
         "1751.36"
        ],
        [
         "广东",
         "1592.19"
        ],
        [
         "广西",
         "1675.06"
        ],
        [
         "新疆",
         "1402.38"
        ],
        [
         "江苏",
         "1279.9"
        ],
        [
         "江西",
         "1966.48"
        ],
        [
         "河北",
         "1780.28"
        ],
        [
         "河南",
         "1558.4"
        ],
        [
         "浙江",
         "1347.28"
        ],
        [
         "海南",
         "1721.98"
        ],
        [
         "湖北",
         "1324.81"
        ],
        [
         "湖南",
         "1603.7"
        ],
        [
         "甘肃",
         "1602.89"
        ],
        [
         "福建",
         "1732.04"
        ],
        [
         "西藏",
         "201.0"
        ],
        [
         "贵州",
         "1425.98"
        ],
        [
         "辽宁",
         "1363.38"
        ],
        [
         "重庆",
         "1383.08"
        ],
        [
         "陕西",
         "1880.61"
        ],
        [
         "青海",
         "2978.95"
        ],
        [
         "黑龙江",
         "1800.44"
        ]
       ],
       "shape": {
        "columns": 1,
        "rows": 31
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>1875.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>1863.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>1314.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>1726.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>1701.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>1157.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>1611.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>1651.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>1477.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>1642.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>1751.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>1592.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>1675.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>1402.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>1279.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>1966.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>1780.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>1558.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>1347.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>1721.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>1324.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>1603.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>1602.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>1732.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>1425.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>1363.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>1383.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>1880.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>2978.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>1800.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           销售额\n",
       "省/自治区         \n",
       "上海     1875.71\n",
       "云南     1863.79\n",
       "内蒙古    1314.83\n",
       "北京     1726.86\n",
       "吉林     1701.56\n",
       "四川     1157.88\n",
       "天津     1611.21\n",
       "宁夏     1651.68\n",
       "安徽     1477.87\n",
       "山东     1642.66\n",
       "山西     1751.36\n",
       "广东     1592.19\n",
       "广西     1675.06\n",
       "新疆     1402.38\n",
       "江苏     1279.90\n",
       "江西     1966.48\n",
       "河北     1780.28\n",
       "河南     1558.40\n",
       "浙江     1347.28\n",
       "海南     1721.98\n",
       "湖北     1324.81\n",
       "湖南     1603.70\n",
       "甘肃     1602.89\n",
       "福建     1732.04\n",
       "西藏      201.00\n",
       "贵州     1425.98\n",
       "辽宁     1363.38\n",
       "重庆     1383.08\n",
       "陕西     1880.61\n",
       "青海     2978.95\n",
       "黑龙江    1800.44"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "if pd.api.types.is_string_dtype(df['销售额']):\n",
    "    df['销售额'] = df['销售额'].str.replace(',', '', regex=False)\n",
    "    df['销售额'] = pd.to_numeric(df['销售额'], errors='coerce')\n",
    "\n",
    "pd.pivot_table(df,values='销售额',index='省/自治区',aggfunc='mean').rename({\n",
    "    '销售额':'平均销售额'\n",
    "}).round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3 - 数据透视｜指定方法\n",
    "\n",
    "制作各省「销售总额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "销售额",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "40998c54-a289-40fb-96c6-8c6fbb1e058b",
       "rows": [
        [
         "上海",
         "594601"
        ],
        [
         "云南",
         "441718"
        ],
        [
         "内蒙古",
         "249817"
        ],
        [
         "北京",
         "350552"
        ],
        [
         "吉林",
         "896724"
        ],
        [
         "四川",
         "269785"
        ],
        [
         "天津",
         "483362"
        ],
        [
         "宁夏",
         "41292"
        ],
        [
         "安徽",
         "684253"
        ],
        [
         "山东",
         "1884130"
        ],
        [
         "山西",
         "429083"
        ],
        [
         "广东",
         "1520537"
        ],
        [
         "广西",
         "383589"
        ],
        [
         "新疆",
         "72924"
        ],
        [
         "江苏",
         "401888"
        ],
        [
         "江西",
         "222212"
        ],
        [
         "河北",
         "899039"
        ],
        [
         "河南",
         "930365"
        ],
        [
         "浙江",
         "266761"
        ],
        [
         "海南",
         "99875"
        ],
        [
         "湖北",
         "408041"
        ],
        [
         "湖南",
         "721666"
        ],
        [
         "甘肃",
         "195553"
        ],
        [
         "福建",
         "620072"
        ],
        [
         "西藏",
         "201"
        ],
        [
         "贵州",
         "67021"
        ],
        [
         "辽宁",
         "756677"
        ],
        [
         "重庆",
         "283532"
        ],
        [
         "陕西",
         "396808"
        ],
        [
         "青海",
         "65537"
        ],
        [
         "黑龙江",
         "1346728"
        ]
       ],
       "shape": {
        "columns": 1,
        "rows": 31
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>594601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>441718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>249817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>350552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>896724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>269785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>483362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>41292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>684253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>1884130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>429083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>1520537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>383589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>72924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>401888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>222212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>899039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>930365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>266761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>99875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>408041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>721666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>195553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>620072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>67021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>756677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>283532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>396808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>65537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>1346728</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           销售额\n",
       "省/自治区         \n",
       "上海      594601\n",
       "云南      441718\n",
       "内蒙古     249817\n",
       "北京      350552\n",
       "吉林      896724\n",
       "四川      269785\n",
       "天津      483362\n",
       "宁夏       41292\n",
       "安徽      684253\n",
       "山东     1884130\n",
       "山西      429083\n",
       "广东     1520537\n",
       "广西      383589\n",
       "新疆       72924\n",
       "江苏      401888\n",
       "江西      222212\n",
       "河北      899039\n",
       "河南      930365\n",
       "浙江      266761\n",
       "海南       99875\n",
       "湖北      408041\n",
       "湖南      721666\n",
       "甘肃      195553\n",
       "福建      620072\n",
       "西藏         201\n",
       "贵州       67021\n",
       "辽宁      756677\n",
       "重庆      283532\n",
       "陕西      396808\n",
       "青海       65537\n",
       "黑龙江    1346728"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,values='销售额',index='省/自治区',aggfunc='sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4 - 数据透视｜多方法\n",
    "\n",
    "制作各省「销售总额」与「平均销售额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "平均销售额",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "销售总额",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "c5a0cfcd-a0f2-45b3-af90-b637237b650a",
       "rows": [
        [
         "上海",
         "1875.71",
         "594601"
        ],
        [
         "云南",
         "1863.79",
         "441718"
        ],
        [
         "内蒙古",
         "1314.83",
         "249817"
        ],
        [
         "北京",
         "1726.86",
         "350552"
        ],
        [
         "吉林",
         "1701.56",
         "896724"
        ],
        [
         "四川",
         "1157.88",
         "269785"
        ],
        [
         "天津",
         "1611.21",
         "483362"
        ],
        [
         "宁夏",
         "1651.68",
         "41292"
        ],
        [
         "安徽",
         "1477.87",
         "684253"
        ],
        [
         "山东",
         "1642.66",
         "1884130"
        ],
        [
         "山西",
         "1751.36",
         "429083"
        ],
        [
         "广东",
         "1592.19",
         "1520537"
        ],
        [
         "广西",
         "1675.06",
         "383589"
        ],
        [
         "新疆",
         "1402.38",
         "72924"
        ],
        [
         "江苏",
         "1279.9",
         "401888"
        ],
        [
         "江西",
         "1966.48",
         "222212"
        ],
        [
         "河北",
         "1780.28",
         "899039"
        ],
        [
         "河南",
         "1558.4",
         "930365"
        ],
        [
         "浙江",
         "1347.28",
         "266761"
        ],
        [
         "海南",
         "1721.98",
         "99875"
        ],
        [
         "湖北",
         "1324.81",
         "408041"
        ],
        [
         "湖南",
         "1603.7",
         "721666"
        ],
        [
         "甘肃",
         "1602.89",
         "195553"
        ],
        [
         "福建",
         "1732.04",
         "620072"
        ],
        [
         "西藏",
         "201.0",
         "201"
        ],
        [
         "贵州",
         "1425.98",
         "67021"
        ],
        [
         "辽宁",
         "1363.38",
         "756677"
        ],
        [
         "重庆",
         "1383.08",
         "283532"
        ],
        [
         "陕西",
         "1880.61",
         "396808"
        ],
        [
         "青海",
         "2978.95",
         "65537"
        ],
        [
         "黑龙江",
         "1800.44",
         "1346728"
        ]
       ],
       "shape": {
        "columns": 2,
        "rows": 31
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>平均销售额</th>\n",
       "      <th>销售总额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>1875.71</td>\n",
       "      <td>594601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>1863.79</td>\n",
       "      <td>441718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>1314.83</td>\n",
       "      <td>249817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>1726.86</td>\n",
       "      <td>350552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>1701.56</td>\n",
       "      <td>896724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>1157.88</td>\n",
       "      <td>269785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>1611.21</td>\n",
       "      <td>483362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>1651.68</td>\n",
       "      <td>41292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>1477.87</td>\n",
       "      <td>684253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>1642.66</td>\n",
       "      <td>1884130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>1751.36</td>\n",
       "      <td>429083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>1592.19</td>\n",
       "      <td>1520537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>1675.06</td>\n",
       "      <td>383589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>1402.38</td>\n",
       "      <td>72924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>1279.90</td>\n",
       "      <td>401888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>1966.48</td>\n",
       "      <td>222212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>1780.28</td>\n",
       "      <td>899039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>1558.40</td>\n",
       "      <td>930365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>1347.28</td>\n",
       "      <td>266761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>1721.98</td>\n",
       "      <td>99875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>1324.81</td>\n",
       "      <td>408041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>1603.70</td>\n",
       "      <td>721666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>1602.89</td>\n",
       "      <td>195553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>1732.04</td>\n",
       "      <td>620072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201.00</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>1425.98</td>\n",
       "      <td>67021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>1363.38</td>\n",
       "      <td>756677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>1383.08</td>\n",
       "      <td>283532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>1880.61</td>\n",
       "      <td>396808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>2978.95</td>\n",
       "      <td>65537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>1800.44</td>\n",
       "      <td>1346728</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         平均销售额     销售总额\n",
       "省/自治区                  \n",
       "上海     1875.71   594601\n",
       "云南     1863.79   441718\n",
       "内蒙古    1314.83   249817\n",
       "北京     1726.86   350552\n",
       "吉林     1701.56   896724\n",
       "四川     1157.88   269785\n",
       "天津     1611.21   483362\n",
       "宁夏     1651.68    41292\n",
       "安徽     1477.87   684253\n",
       "山东     1642.66  1884130\n",
       "山西     1751.36   429083\n",
       "广东     1592.19  1520537\n",
       "广西     1675.06   383589\n",
       "新疆     1402.38    72924\n",
       "江苏     1279.90   401888\n",
       "江西     1966.48   222212\n",
       "河北     1780.28   899039\n",
       "河南     1558.40   930365\n",
       "浙江     1347.28   266761\n",
       "海南     1721.98    99875\n",
       "湖北     1324.81   408041\n",
       "湖南     1603.70   721666\n",
       "甘肃     1602.89   195553\n",
       "福建     1732.04   620072\n",
       "西藏      201.00      201\n",
       "贵州     1425.98    67021\n",
       "辽宁     1363.38   756677\n",
       "重庆     1383.08   283532\n",
       "陕西     1880.61   396808\n",
       "青海     2978.95    65537\n",
       "黑龙江    1800.44  1346728"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf=pd.pivot_table(df,values='销售额',index='省/自治区',aggfunc=['mean','sum']).round(2)\n",
    "ndf.columns=['平均销售额','销售总额']\n",
    "ndf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5 - 数据透视｜多指标\n",
    "\n",
    "制作各省市「销售总额」与「利润总额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "销售总额",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "利润总额",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "d6eb3499-1804-4e83-88d0-334c5085e5d2",
       "rows": [
        [
         "湖南",
         "721666",
         "92944"
        ],
        [
         "福建",
         "620072",
         "133791"
        ],
        [
         "辽宁",
         "756677",
         "-24930"
        ],
        [
         "广西",
         "383589",
         "61444"
        ],
        [
         "北京",
         "350552",
         "57883"
        ],
        [
         "云南",
         "441718",
         "83201"
        ],
        [
         "河北",
         "899039",
         "153247"
        ],
        [
         "黑龙江",
         "1346728",
         "228262"
        ],
        [
         "吉林",
         "896724",
         "152504"
        ],
        [
         "广东",
         "1520537",
         "278591"
        ],
        [
         "山东",
         "1884130",
         "310042"
        ],
        [
         "上海",
         "594601",
         "87236"
        ],
        [
         "天津",
         "483362",
         "63108"
        ],
        [
         "陕西",
         "396808",
         "61753"
        ],
        [
         "山西",
         "429083",
         "79913"
        ],
        [
         "河南",
         "930365",
         "168714"
        ],
        [
         "安徽",
         "684253",
         "133312"
        ],
        [
         "重庆",
         "283532",
         "39688"
        ],
        [
         "湖北",
         "408041",
         "-22896"
        ],
        [
         "江苏",
         "401888",
         "-6500"
        ],
        [
         "内蒙古",
         "249817",
         "-20685"
        ],
        [
         "四川",
         "269785",
         "-16615"
        ],
        [
         "甘肃",
         "195553",
         "-25298"
        ],
        [
         "浙江",
         "266761",
         "-17024"
        ],
        [
         "海南",
         "99875",
         "21298"
        ],
        [
         "贵州",
         "67021",
         "5408"
        ],
        [
         "江西",
         "222212",
         "27144"
        ],
        [
         "新疆",
         "72924",
         "13696"
        ],
        [
         "宁夏",
         "41292",
         "-1149"
        ],
        [
         "西藏",
         "201",
         "4"
        ],
        [
         "青海",
         "65537",
         "4354"
        ]
       ],
       "shape": {
        "columns": 2,
        "rows": 31
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售总额</th>\n",
       "      <th>利润总额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>721666</td>\n",
       "      <td>92944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>620072</td>\n",
       "      <td>133791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>756677</td>\n",
       "      <td>-24930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>383589</td>\n",
       "      <td>61444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>350552</td>\n",
       "      <td>57883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>441718</td>\n",
       "      <td>83201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>899039</td>\n",
       "      <td>153247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>1346728</td>\n",
       "      <td>228262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>896724</td>\n",
       "      <td>152504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>1520537</td>\n",
       "      <td>278591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>1884130</td>\n",
       "      <td>310042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>594601</td>\n",
       "      <td>87236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>483362</td>\n",
       "      <td>63108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>396808</td>\n",
       "      <td>61753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>429083</td>\n",
       "      <td>79913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>930365</td>\n",
       "      <td>168714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>684253</td>\n",
       "      <td>133312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>283532</td>\n",
       "      <td>39688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>408041</td>\n",
       "      <td>-22896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>401888</td>\n",
       "      <td>-6500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>249817</td>\n",
       "      <td>-20685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>269785</td>\n",
       "      <td>-16615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>195553</td>\n",
       "      <td>-25298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>266761</td>\n",
       "      <td>-17024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>99875</td>\n",
       "      <td>21298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>67021</td>\n",
       "      <td>5408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>222212</td>\n",
       "      <td>27144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>72924</td>\n",
       "      <td>13696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>41292</td>\n",
       "      <td>-1149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>65537</td>\n",
       "      <td>4354</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          销售总额    利润总额\n",
       "省/自治区                 \n",
       "湖南      721666   92944\n",
       "福建      620072  133791\n",
       "辽宁      756677  -24930\n",
       "广西      383589   61444\n",
       "北京      350552   57883\n",
       "云南      441718   83201\n",
       "河北      899039  153247\n",
       "黑龙江    1346728  228262\n",
       "吉林      896724  152504\n",
       "广东     1520537  278591\n",
       "山东     1884130  310042\n",
       "上海      594601   87236\n",
       "天津      483362   63108\n",
       "陕西      396808   61753\n",
       "山西      429083   79913\n",
       "河南      930365  168714\n",
       "安徽      684253  133312\n",
       "重庆      283532   39688\n",
       "湖北      408041  -22896\n",
       "江苏      401888   -6500\n",
       "内蒙古     249817  -20685\n",
       "四川      269785  -16615\n",
       "甘肃      195553  -25298\n",
       "浙江      266761  -17024\n",
       "海南       99875   21298\n",
       "贵州       67021    5408\n",
       "江西      222212   27144\n",
       "新疆       72924   13696\n",
       "宁夏       41292   -1149\n",
       "西藏         201       4\n",
       "青海       65537    4354"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf=pd.pivot_table(df,values=['销售额','利润'],index='省/自治区',aggfunc='sum',sort=False)\n",
    "ndf.columns=['销售总额','利润总额']\n",
    "ndf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当你像这样传入一个列名列表 `values=['销售额','利润']` 时，`pivot_table` 会为每个列创建一个新的列层级，从而生成一个多级索引（`MultiIndex`）。\n",
    "\n",
    "关键在于，Pandas 在默认情况下（除非特别指定 `sort=False`），**会自动对这个新生成的多级列索引按字母顺序进行排序**。这个过程与你传入列表的顺序以及原始 DataFrame 的列顺序无关。排序发生在透视表结构构建之后。\n",
    "\n",
    "所以，尽管你指定的是 `['销售额','利润']`，但因为在中文拼音或内部编码中，“利 (Lì)” 的排序通常在“销 (Xiāo)”之前，导致最终列的顺序变成了 `['利润', '销售额']`。\n",
    "\n",
    "解决方案\n",
    "\n",
    "要控制生成透视表的列顺序，有以下几种可靠的方法：\n",
    "\n",
    "1.  **使用 `sort=False` 参数（最直接）**\n",
    "    在 `pivot_table` 函数中设置 `sort=False` 可以禁止自动排序。这是最快捷的解决方法。\n",
    "    ```python\n",
    "    ndf = pd.pivot_table(df, values=['销售额','利润'], index='省/自治区', aggfunc='sum', sort=False)\n",
    "    ```\n",
    "\n",
    "2.  **手动调整列顺序**\n",
    "    如果出于某种原因必须允许排序，或者已经生成了透视表，可以在创建后使用 `.reindex()` 方法手动指定你需要的列顺序。\n",
    "    ```python\n",
    "    # 先创建透视表（列顺序可能被打乱）\n",
    "    ndf = pd.pivot_table(df, values=['销售额','利润'], index='省/自治区', aggfunc='sum')\n",
    "    # 然后按照你期望的顺序重新排列列\n",
    "    desired_order = ['销售额', '利润']\n",
    "    ndf = ndf.reindex(columns=desired_order, level=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6 - 数据透视｜多索引\n",
    "\n",
    "制作「各省市」与「不同类别」产品「销售总额」的数据透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "类别",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "销售总额",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "3c9432f2-0567-4ad6-91a6-27cd4e6a1a1d",
       "rows": [
        [
         "0",
         "上海",
         "办公用品",
         "198529"
        ],
        [
         "1",
         "上海",
         "家具",
         "221058"
        ],
        [
         "2",
         "上海",
         "技术",
         "175014"
        ],
        [
         "3",
         "云南",
         "办公用品",
         "123051"
        ],
        [
         "4",
         "云南",
         "家具",
         "174155"
        ],
        [
         "5",
         "云南",
         "技术",
         "144512"
        ],
        [
         "6",
         "内蒙古",
         "办公用品",
         "74058"
        ],
        [
         "7",
         "内蒙古",
         "家具",
         "95426"
        ],
        [
         "8",
         "内蒙古",
         "技术",
         "80333"
        ],
        [
         "9",
         "北京",
         "办公用品",
         "144232"
        ],
        [
         "10",
         "北京",
         "家具",
         "127407"
        ],
        [
         "11",
         "北京",
         "技术",
         "78913"
        ],
        [
         "12",
         "吉林",
         "办公用品",
         "215143"
        ],
        [
         "13",
         "吉林",
         "家具",
         "287498"
        ],
        [
         "14",
         "吉林",
         "技术",
         "394083"
        ],
        [
         "15",
         "四川",
         "办公用品",
         "111393"
        ],
        [
         "16",
         "四川",
         "家具",
         "88297"
        ],
        [
         "17",
         "四川",
         "技术",
         "70095"
        ],
        [
         "18",
         "天津",
         "办公用品",
         "142526"
        ],
        [
         "19",
         "天津",
         "家具",
         "149452"
        ],
        [
         "20",
         "天津",
         "技术",
         "191384"
        ],
        [
         "21",
         "宁夏",
         "办公用品",
         "19529"
        ],
        [
         "22",
         "宁夏",
         "家具",
         "16449"
        ],
        [
         "23",
         "宁夏",
         "技术",
         "5314"
        ],
        [
         "24",
         "安徽",
         "办公用品",
         "200511"
        ],
        [
         "25",
         "安徽",
         "家具",
         "215901"
        ],
        [
         "26",
         "安徽",
         "技术",
         "267841"
        ],
        [
         "27",
         "山东",
         "办公用品",
         "575520"
        ],
        [
         "28",
         "山东",
         "家具",
         "664339"
        ],
        [
         "29",
         "山东",
         "技术",
         "644271"
        ],
        [
         "30",
         "山西",
         "办公用品",
         "121458"
        ],
        [
         "31",
         "山西",
         "家具",
         "175522"
        ],
        [
         "32",
         "山西",
         "技术",
         "132103"
        ],
        [
         "33",
         "广东",
         "办公用品",
         "494643"
        ],
        [
         "34",
         "广东",
         "家具",
         "530054"
        ],
        [
         "35",
         "广东",
         "技术",
         "495840"
        ],
        [
         "36",
         "广西",
         "办公用品",
         "102625"
        ],
        [
         "37",
         "广西",
         "家具",
         "165140"
        ],
        [
         "38",
         "广西",
         "技术",
         "115824"
        ],
        [
         "39",
         "新疆",
         "办公用品",
         "38345"
        ],
        [
         "40",
         "新疆",
         "家具",
         "20520"
        ],
        [
         "41",
         "新疆",
         "技术",
         "14059"
        ],
        [
         "42",
         "江苏",
         "办公用品",
         "163919"
        ],
        [
         "43",
         "江苏",
         "家具",
         "152868"
        ],
        [
         "44",
         "江苏",
         "技术",
         "85101"
        ],
        [
         "45",
         "江西",
         "办公用品",
         "37114"
        ],
        [
         "46",
         "江西",
         "家具",
         "107047"
        ],
        [
         "47",
         "江西",
         "技术",
         "78051"
        ],
        [
         "48",
         "河北",
         "办公用品",
         "284739"
        ],
        [
         "49",
         "河北",
         "家具",
         "306535"
        ]
       ],
       "shape": {
        "columns": 3,
        "rows": 91
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省/自治区</th>\n",
       "      <th>类别</th>\n",
       "      <th>销售总额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海</td>\n",
       "      <td>办公用品</td>\n",
       "      <td>198529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>上海</td>\n",
       "      <td>家具</td>\n",
       "      <td>221058</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>上海</td>\n",
       "      <td>技术</td>\n",
       "      <td>175014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>云南</td>\n",
       "      <td>办公用品</td>\n",
       "      <td>123051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>云南</td>\n",
       "      <td>家具</td>\n",
       "      <td>174155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>青海</td>\n",
       "      <td>家具</td>\n",
       "      <td>25923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>青海</td>\n",
       "      <td>技术</td>\n",
       "      <td>22896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>黑龙江</td>\n",
       "      <td>办公用品</td>\n",
       "      <td>473319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>黑龙江</td>\n",
       "      <td>家具</td>\n",
       "      <td>497504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>黑龙江</td>\n",
       "      <td>技术</td>\n",
       "      <td>375905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   省/自治区    类别    销售总额\n",
       "0     上海  办公用品  198529\n",
       "1     上海    家具  221058\n",
       "2     上海    技术  175014\n",
       "3     云南  办公用品  123051\n",
       "4     云南    家具  174155\n",
       "..   ...   ...     ...\n",
       "86    青海    家具   25923\n",
       "87    青海    技术   22896\n",
       "88   黑龙江  办公用品  473319\n",
       "89   黑龙江    家具  497504\n",
       "90   黑龙江    技术  375905\n",
       "\n",
       "[91 rows x 3 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf=pd.pivot_table(df,values='销售额',index=['省/自治区','类别'],aggfunc='sum').reset_index()\n",
    "ndf.columns=['销售总额' if col=='销售额' else col for col in ndf.columns]\n",
    "ndf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7 - 数据透视｜多层\n",
    "\n",
    "制作各省市「不同类别」产品的「销售总额」透视表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "办公用品",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "家具",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "技术",
         "rawType": "float64",
         "type": "float"
        }
       ],
       "ref": "592207fd-6786-4070-aed0-3cedbd5398c5",
       "rows": [
        [
         "上海",
         "198529.0",
         "221058.0",
         "175014.0"
        ],
        [
         "云南",
         "123051.0",
         "174155.0",
         "144512.0"
        ],
        [
         "内蒙古",
         "74058.0",
         "95426.0",
         "80333.0"
        ],
        [
         "北京",
         "144232.0",
         "127407.0",
         "78913.0"
        ],
        [
         "吉林",
         "215143.0",
         "287498.0",
         "394083.0"
        ],
        [
         "四川",
         "111393.0",
         "88297.0",
         "70095.0"
        ],
        [
         "天津",
         "142526.0",
         "149452.0",
         "191384.0"
        ],
        [
         "宁夏",
         "19529.0",
         "16449.0",
         "5314.0"
        ],
        [
         "安徽",
         "200511.0",
         "215901.0",
         "267841.0"
        ],
        [
         "山东",
         "575520.0",
         "664339.0",
         "644271.0"
        ],
        [
         "山西",
         "121458.0",
         "175522.0",
         "132103.0"
        ],
        [
         "广东",
         "494643.0",
         "530054.0",
         "495840.0"
        ],
        [
         "广西",
         "102625.0",
         "165140.0",
         "115824.0"
        ],
        [
         "新疆",
         "38345.0",
         "20520.0",
         "14059.0"
        ],
        [
         "江苏",
         "163919.0",
         "152868.0",
         "85101.0"
        ],
        [
         "江西",
         "37114.0",
         "107047.0",
         "78051.0"
        ],
        [
         "河北",
         "284739.0",
         "306535.0",
         "307765.0"
        ],
        [
         "河南",
         "266916.0",
         "294593.0",
         "368856.0"
        ],
        [
         "浙江",
         "84471.0",
         "84436.0",
         "97854.0"
        ],
        [
         "海南",
         "34141.0",
         "41225.0",
         "24509.0"
        ],
        [
         "湖北",
         "112230.0",
         "118053.0",
         "177758.0"
        ],
        [
         "湖南",
         "197969.0",
         "241804.0",
         "281893.0"
        ],
        [
         "甘肃",
         "54012.0",
         "68657.0",
         "72884.0"
        ],
        [
         "福建",
         "142728.0",
         "243289.0",
         "234055.0"
        ],
        [
         "西藏",
         "201.0",
         null,
         null
        ],
        [
         "贵州",
         "9713.0",
         "29285.0",
         "28023.0"
        ],
        [
         "辽宁",
         "214994.0",
         "270279.0",
         "271404.0"
        ],
        [
         "重庆",
         "71020.0",
         "96318.0",
         "116194.0"
        ],
        [
         "陕西",
         "119169.0",
         "187497.0",
         "90142.0"
        ],
        [
         "青海",
         "16718.0",
         "25923.0",
         "22896.0"
        ],
        [
         "黑龙江",
         "473319.0",
         "497504.0",
         "375905.0"
        ]
       ],
       "shape": {
        "columns": 3,
        "rows": 31
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>类别</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>家具</th>\n",
       "      <th>技术</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>198529.0</td>\n",
       "      <td>221058.0</td>\n",
       "      <td>175014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>123051.0</td>\n",
       "      <td>174155.0</td>\n",
       "      <td>144512.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>74058.0</td>\n",
       "      <td>95426.0</td>\n",
       "      <td>80333.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>144232.0</td>\n",
       "      <td>127407.0</td>\n",
       "      <td>78913.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>215143.0</td>\n",
       "      <td>287498.0</td>\n",
       "      <td>394083.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>111393.0</td>\n",
       "      <td>88297.0</td>\n",
       "      <td>70095.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>142526.0</td>\n",
       "      <td>149452.0</td>\n",
       "      <td>191384.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>19529.0</td>\n",
       "      <td>16449.0</td>\n",
       "      <td>5314.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>200511.0</td>\n",
       "      <td>215901.0</td>\n",
       "      <td>267841.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>575520.0</td>\n",
       "      <td>664339.0</td>\n",
       "      <td>644271.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>121458.0</td>\n",
       "      <td>175522.0</td>\n",
       "      <td>132103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>494643.0</td>\n",
       "      <td>530054.0</td>\n",
       "      <td>495840.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>102625.0</td>\n",
       "      <td>165140.0</td>\n",
       "      <td>115824.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>38345.0</td>\n",
       "      <td>20520.0</td>\n",
       "      <td>14059.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>163919.0</td>\n",
       "      <td>152868.0</td>\n",
       "      <td>85101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>37114.0</td>\n",
       "      <td>107047.0</td>\n",
       "      <td>78051.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>284739.0</td>\n",
       "      <td>306535.0</td>\n",
       "      <td>307765.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>266916.0</td>\n",
       "      <td>294593.0</td>\n",
       "      <td>368856.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>84471.0</td>\n",
       "      <td>84436.0</td>\n",
       "      <td>97854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>34141.0</td>\n",
       "      <td>41225.0</td>\n",
       "      <td>24509.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>112230.0</td>\n",
       "      <td>118053.0</td>\n",
       "      <td>177758.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>197969.0</td>\n",
       "      <td>241804.0</td>\n",
       "      <td>281893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>54012.0</td>\n",
       "      <td>68657.0</td>\n",
       "      <td>72884.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>142728.0</td>\n",
       "      <td>243289.0</td>\n",
       "      <td>234055.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>201.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>9713.0</td>\n",
       "      <td>29285.0</td>\n",
       "      <td>28023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>214994.0</td>\n",
       "      <td>270279.0</td>\n",
       "      <td>271404.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>71020.0</td>\n",
       "      <td>96318.0</td>\n",
       "      <td>116194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>119169.0</td>\n",
       "      <td>187497.0</td>\n",
       "      <td>90142.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>16718.0</td>\n",
       "      <td>25923.0</td>\n",
       "      <td>22896.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>473319.0</td>\n",
       "      <td>497504.0</td>\n",
       "      <td>375905.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "类别         办公用品        家具        技术\n",
       "省/自治区                              \n",
       "上海     198529.0  221058.0  175014.0\n",
       "云南     123051.0  174155.0  144512.0\n",
       "内蒙古     74058.0   95426.0   80333.0\n",
       "北京     144232.0  127407.0   78913.0\n",
       "吉林     215143.0  287498.0  394083.0\n",
       "四川     111393.0   88297.0   70095.0\n",
       "天津     142526.0  149452.0  191384.0\n",
       "宁夏      19529.0   16449.0    5314.0\n",
       "安徽     200511.0  215901.0  267841.0\n",
       "山东     575520.0  664339.0  644271.0\n",
       "山西     121458.0  175522.0  132103.0\n",
       "广东     494643.0  530054.0  495840.0\n",
       "广西     102625.0  165140.0  115824.0\n",
       "新疆      38345.0   20520.0   14059.0\n",
       "江苏     163919.0  152868.0   85101.0\n",
       "江西      37114.0  107047.0   78051.0\n",
       "河北     284739.0  306535.0  307765.0\n",
       "河南     266916.0  294593.0  368856.0\n",
       "浙江      84471.0   84436.0   97854.0\n",
       "海南      34141.0   41225.0   24509.0\n",
       "湖北     112230.0  118053.0  177758.0\n",
       "湖南     197969.0  241804.0  281893.0\n",
       "甘肃      54012.0   68657.0   72884.0\n",
       "福建     142728.0  243289.0  234055.0\n",
       "西藏        201.0       NaN       NaN\n",
       "贵州       9713.0   29285.0   28023.0\n",
       "辽宁     214994.0  270279.0  271404.0\n",
       "重庆      71020.0   96318.0  116194.0\n",
       "陕西     119169.0  187497.0   90142.0\n",
       "青海      16718.0   25923.0   22896.0\n",
       "黑龙江    473319.0  497504.0  375905.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df,values='销售额',index='省/自治区',columns='类别',aggfunc='sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8 - 数据透视｜综合\n",
    "\n",
    "制作「各省市」、「不同类别」产品「销售量与销售额」的「均值与总和」的数据透视表，并在最后追加一行『合计』"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "('mean', '数量', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '数量', '家具')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '数量', '技术')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '数量', 'All')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '销售额', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '销售额', '家具')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '销售额', '技术')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '销售额', 'All')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '数量', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '数量', '家具')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '数量', '技术')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '数量', 'All')",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "('sum', '销售额', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '销售额', '家具')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '销售额', '技术')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '销售额', 'All')",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "7cd75c99-0a0b-4217-af0a-8f7466c4d886",
       "rows": [
        [
         "上海",
         "3.71",
         "4.13",
         "3.62",
         "3.8",
         "1140.97",
         "2663.35",
         "2916.9",
         "1875.71",
         "645.0",
         "343.0",
         "217.0",
         "1205",
         "198529.0",
         "221058.0",
         "175014.0",
         "594601"
        ],
        [
         "云南",
         "3.91",
         "4.22",
         "3.88",
         "3.97",
         "891.67",
         "3554.18",
         "2890.24",
         "1863.79",
         "540.0",
         "207.0",
         "194.0",
         "941",
         "123051.0",
         "174155.0",
         "144512.0",
         "441718"
        ],
        [
         "内蒙古",
         "3.39",
         "3.36",
         "3.42",
         "3.39",
         "643.98",
         "2168.77",
         "2591.39",
         "1314.83",
         "390.0",
         "148.0",
         "106.0",
         "644",
         "74058.0",
         "95426.0",
         "80333.0",
         "249817"
        ],
        [
         "北京",
         "3.75",
         "3.7",
         "3.48",
         "3.69",
         "1163.16",
         "2769.72",
         "2391.3",
         "1726.86",
         "465.0",
         "170.0",
         "115.0",
         "750",
         "144232.0",
         "127407.0",
         "78913.0",
         "350552"
        ],
        [
         "吉林",
         "3.52",
         "3.77",
         "4.08",
         "3.7",
         "726.83",
         "2712.25",
         "3152.66",
         "1701.56",
         "1042.0",
         "400.0",
         "510.0",
         "1952",
         "215143.0",
         "287498.0",
         "394083.0",
         "896724"
        ],
        [
         "四川",
         "3.95",
         "3.25",
         "3.23",
         "3.64",
         "837.54",
         "1447.49",
         "1797.31",
         "1157.88",
         "525.0",
         "198.0",
         "126.0",
         "849",
         "111393.0",
         "88297.0",
         "70095.0",
         "269785"
        ],
        [
         "天津",
         "3.92",
         "4.21",
         "3.59",
         "3.91",
         "787.44",
         "2576.76",
         "3137.44",
         "1611.21",
         "709.0",
         "244.0",
         "219.0",
         "1172",
         "142526.0",
         "149452.0",
         "191384.0",
         "483362"
        ],
        [
         "宁夏",
         "3.75",
         "4.56",
         "3.25",
         "3.96",
         "1627.42",
         "1827.67",
         "1328.5",
         "1651.68",
         "45.0",
         "41.0",
         "13.0",
         "99",
         "19529.0",
         "16449.0",
         "5314.0",
         "41292"
        ],
        [
         "安徽",
         "3.71",
         "3.49",
         "3.58",
         "3.63",
         "753.8",
         "2137.63",
         "2790.01",
         "1477.87",
         "987.0",
         "352.0",
         "344.0",
         "1683",
         "200511.0",
         "215901.0",
         "267841.0",
         "684253"
        ],
        [
         "山东",
         "3.78",
         "3.73",
         "3.75",
         "3.76",
         "862.85",
         "2605.25",
         "2863.43",
         "1642.66",
         "2518.0",
         "952.0",
         "843.0",
         "4313",
         "575520.0",
         "664339.0",
         "644271.0",
         "1884130"
        ],
        [
         "山西",
         "3.88",
         "3.78",
         "3.72",
         "3.82",
         "906.4",
         "3510.44",
         "2165.62",
         "1751.36",
         "520.0",
         "189.0",
         "227.0",
         "936",
         "121458.0",
         "175522.0",
         "132103.0",
         "429083"
        ],
        [
         "广东",
         "3.91",
         "3.56",
         "3.82",
         "3.81",
         "909.27",
         "2536.14",
         "2454.65",
         "1592.19",
         "2126.0",
         "743.0",
         "771.0",
         "3640",
         "494643.0",
         "530054.0",
         "495840.0",
         "1520537"
        ],
        [
         "广西",
         "3.95",
         "4.03",
         "4.56",
         "4.08",
         "777.46",
         "2847.24",
         "2969.85",
         "1675.06",
         "522.0",
         "234.0",
         "178.0",
         "934",
         "102625.0",
         "165140.0",
         "115824.0",
         "383589"
        ],
        [
         "新疆",
         "3.58",
         "4.0",
         "4.5",
         "3.81",
         "1161.97",
         "1865.45",
         "1757.38",
         "1402.38",
         "118.0",
         "44.0",
         "36.0",
         "198",
         "38345.0",
         "20520.0",
         "14059.0",
         "72924"
        ],
        [
         "江苏",
         "3.85",
         "3.79",
         "3.9",
         "3.84",
         "849.32",
         "2094.08",
         "1772.94",
         "1279.9",
         "743.0",
         "277.0",
         "187.0",
         "1207",
         "163919.0",
         "152868.0",
         "85101.0",
         "401888"
        ],
        [
         "江西",
         "3.52",
         "4.79",
         "3.79",
         "3.85",
         "570.98",
         "4460.29",
         "3252.12",
         "1966.48",
         "229.0",
         "115.0",
         "91.0",
         "435",
         "37114.0",
         "107047.0",
         "78051.0",
         "222212"
        ],
        [
         "河北",
         "3.97",
         "3.62",
         "3.58",
         "3.81",
         "988.68",
         "2736.92",
         "2931.1",
         "1780.28",
         "1143.0",
         "405.0",
         "376.0",
         "1924",
         "284739.0",
         "306535.0",
         "307765.0",
         "899039"
        ],
        [
         "河南",
         "3.63",
         "3.52",
         "4.01",
         "3.68",
         "769.21",
         "2319.63",
         "2998.83",
         "1558.4",
         "1259.0",
         "447.0",
         "493.0",
         "2199",
         "266916.0",
         "294593.0",
         "368856.0",
         "930365"
        ],
        [
         "浙江",
         "3.68",
         "3.53",
         "3.63",
         "3.64",
         "740.97",
         "1963.63",
         "2386.68",
         "1347.28",
         "420.0",
         "152.0",
         "149.0",
         "721",
         "84471.0",
         "84436.0",
         "97854.0",
         "266761"
        ],
        [
         "海南",
         "3.31",
         "3.93",
         "3.33",
         "3.47",
         "975.46",
         "2944.64",
         "2723.22",
         "1721.98",
         "116.0",
         "55.0",
         "30.0",
         "201",
         "34141.0",
         "41225.0",
         "24509.0",
         "99875"
        ],
        [
         "湖北",
         "3.67",
         "3.49",
         "3.95",
         "3.69",
         "645.0",
         "1736.07",
         "2693.3",
         "1324.81",
         "638.0",
         "237.0",
         "261.0",
         "1136",
         "112230.0",
         "118053.0",
         "177758.0",
         "408041"
        ],
        [
         "湖南",
         "3.63",
         "3.99",
         "3.9",
         "3.76",
         "755.61",
         "2747.77",
         "2818.93",
         "1603.7",
         "951.0",
         "351.0",
         "390.0",
         "1692",
         "197969.0",
         "241804.0",
         "281893.0",
         "721666"
        ],
        [
         "甘肃",
         "4.21",
         "4.47",
         "4.63",
         "4.37",
         "857.33",
         "2145.53",
         "2699.41",
         "1602.89",
         "265.0",
         "143.0",
         "125.0",
         "533",
         "54012.0",
         "68657.0",
         "72884.0",
         "195553"
        ],
        [
         "福建",
         "3.8",
         "3.67",
         "3.44",
         "3.69",
         "747.27",
         "2588.18",
         "3206.23",
         "1732.04",
         "726.0",
         "345.0",
         "251.0",
         "1322",
         "142728.0",
         "243289.0",
         "234055.0",
         "620072"
        ],
        [
         "西藏",
         "3.0",
         null,
         null,
         "3.0",
         "201.0",
         null,
         null,
         "201.0",
         "3.0",
         null,
         null,
         "3",
         "201.0",
         null,
         null,
         "201"
        ],
        [
         "贵州",
         "3.95",
         "4.42",
         "4.15",
         "4.13",
         "441.5",
         "2440.42",
         "2155.62",
         "1425.98",
         "87.0",
         "53.0",
         "54.0",
         "194",
         "9713.0",
         "29285.0",
         "28023.0",
         "67021"
        ],
        [
         "辽宁",
         "3.74",
         "4.06",
         "3.94",
         "3.86",
         "711.9",
         "2095.19",
         "2188.74",
         "1363.38",
         "1130.0",
         "524.0",
         "488.0",
         "2142",
         "214994.0",
         "270279.0",
         "271404.0",
         "756677"
        ],
        [
         "重庆",
         "3.88",
         "3.57",
         "3.98",
         "3.82",
         "628.5",
         "1888.59",
         "2834.0",
         "1383.08",
         "438.0",
         "182.0",
         "163.0",
         "783",
         "71020.0",
         "96318.0",
         "116194.0",
         "283532"
        ],
        [
         "陕西",
         "3.82",
         "4.06",
         "3.26",
         "3.77",
         "993.08",
         "3605.71",
         "2311.33",
         "1880.61",
         "458.0",
         "211.0",
         "127.0",
         "796",
         "119169.0",
         "187497.0",
         "90142.0",
         "396808"
        ],
        [
         "青海",
         "4.44",
         "3.0",
         "3.83",
         "3.82",
         "1857.56",
         "3703.29",
         "3816.0",
         "2978.95",
         "40.0",
         "21.0",
         "23.0",
         "84",
         "16718.0",
         "25923.0",
         "22896.0",
         "65537"
        ],
        [
         "黑龙江",
         "3.66",
         "3.92",
         "3.44",
         "3.67",
         "1088.09",
         "2997.01",
         "2557.18",
         "1800.44",
         "1591.0",
         "651.0",
         "506.0",
         "2748",
         "473319.0",
         "497504.0",
         "375905.0",
         "1346728"
        ],
        [
         "All",
         "3.76",
         "3.78",
         "3.77",
         "3.77",
         "852.53",
         "2552.21",
         "2694.49",
         "1608.89",
         "21389.0",
         "8434.0",
         "7613.0",
         "37436",
         "4844936.0",
         "5696531.0",
         "5442876.0",
         "15984343"
        ]
       ],
       "shape": {
        "columns": 16,
        "rows": 32
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"8\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">数量</th>\n",
       "      <th colspan=\"4\" halign=\"left\">销售额</th>\n",
       "      <th colspan=\"4\" halign=\"left\">数量</th>\n",
       "      <th colspan=\"4\" halign=\"left\">销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>类别</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>家具</th>\n",
       "      <th>技术</th>\n",
       "      <th>All</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>家具</th>\n",
       "      <th>技术</th>\n",
       "      <th>All</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>家具</th>\n",
       "      <th>技术</th>\n",
       "      <th>All</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>家具</th>\n",
       "      <th>技术</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>3.71</td>\n",
       "      <td>4.13</td>\n",
       "      <td>3.62</td>\n",
       "      <td>3.80</td>\n",
       "      <td>1140.97</td>\n",
       "      <td>2663.35</td>\n",
       "      <td>2916.90</td>\n",
       "      <td>1875.71</td>\n",
       "      <td>645.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>1205</td>\n",
       "      <td>198529.0</td>\n",
       "      <td>221058.0</td>\n",
       "      <td>175014.0</td>\n",
       "      <td>594601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>3.91</td>\n",
       "      <td>4.22</td>\n",
       "      <td>3.88</td>\n",
       "      <td>3.97</td>\n",
       "      <td>891.67</td>\n",
       "      <td>3554.18</td>\n",
       "      <td>2890.24</td>\n",
       "      <td>1863.79</td>\n",
       "      <td>540.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>194.0</td>\n",
       "      <td>941</td>\n",
       "      <td>123051.0</td>\n",
       "      <td>174155.0</td>\n",
       "      <td>144512.0</td>\n",
       "      <td>441718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>3.39</td>\n",
       "      <td>3.36</td>\n",
       "      <td>3.42</td>\n",
       "      <td>3.39</td>\n",
       "      <td>643.98</td>\n",
       "      <td>2168.77</td>\n",
       "      <td>2591.39</td>\n",
       "      <td>1314.83</td>\n",
       "      <td>390.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>644</td>\n",
       "      <td>74058.0</td>\n",
       "      <td>95426.0</td>\n",
       "      <td>80333.0</td>\n",
       "      <td>249817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>3.75</td>\n",
       "      <td>3.70</td>\n",
       "      <td>3.48</td>\n",
       "      <td>3.69</td>\n",
       "      <td>1163.16</td>\n",
       "      <td>2769.72</td>\n",
       "      <td>2391.30</td>\n",
       "      <td>1726.86</td>\n",
       "      <td>465.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>750</td>\n",
       "      <td>144232.0</td>\n",
       "      <td>127407.0</td>\n",
       "      <td>78913.0</td>\n",
       "      <td>350552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>3.52</td>\n",
       "      <td>3.77</td>\n",
       "      <td>4.08</td>\n",
       "      <td>3.70</td>\n",
       "      <td>726.83</td>\n",
       "      <td>2712.25</td>\n",
       "      <td>3152.66</td>\n",
       "      <td>1701.56</td>\n",
       "      <td>1042.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>510.0</td>\n",
       "      <td>1952</td>\n",
       "      <td>215143.0</td>\n",
       "      <td>287498.0</td>\n",
       "      <td>394083.0</td>\n",
       "      <td>896724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>3.95</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.23</td>\n",
       "      <td>3.64</td>\n",
       "      <td>837.54</td>\n",
       "      <td>1447.49</td>\n",
       "      <td>1797.31</td>\n",
       "      <td>1157.88</td>\n",
       "      <td>525.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>849</td>\n",
       "      <td>111393.0</td>\n",
       "      <td>88297.0</td>\n",
       "      <td>70095.0</td>\n",
       "      <td>269785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>3.92</td>\n",
       "      <td>4.21</td>\n",
       "      <td>3.59</td>\n",
       "      <td>3.91</td>\n",
       "      <td>787.44</td>\n",
       "      <td>2576.76</td>\n",
       "      <td>3137.44</td>\n",
       "      <td>1611.21</td>\n",
       "      <td>709.0</td>\n",
       "      <td>244.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>1172</td>\n",
       "      <td>142526.0</td>\n",
       "      <td>149452.0</td>\n",
       "      <td>191384.0</td>\n",
       "      <td>483362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>3.75</td>\n",
       "      <td>4.56</td>\n",
       "      <td>3.25</td>\n",
       "      <td>3.96</td>\n",
       "      <td>1627.42</td>\n",
       "      <td>1827.67</td>\n",
       "      <td>1328.50</td>\n",
       "      <td>1651.68</td>\n",
       "      <td>45.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>99</td>\n",
       "      <td>19529.0</td>\n",
       "      <td>16449.0</td>\n",
       "      <td>5314.0</td>\n",
       "      <td>41292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>3.71</td>\n",
       "      <td>3.49</td>\n",
       "      <td>3.58</td>\n",
       "      <td>3.63</td>\n",
       "      <td>753.80</td>\n",
       "      <td>2137.63</td>\n",
       "      <td>2790.01</td>\n",
       "      <td>1477.87</td>\n",
       "      <td>987.0</td>\n",
       "      <td>352.0</td>\n",
       "      <td>344.0</td>\n",
       "      <td>1683</td>\n",
       "      <td>200511.0</td>\n",
       "      <td>215901.0</td>\n",
       "      <td>267841.0</td>\n",
       "      <td>684253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>3.78</td>\n",
       "      <td>3.73</td>\n",
       "      <td>3.75</td>\n",
       "      <td>3.76</td>\n",
       "      <td>862.85</td>\n",
       "      <td>2605.25</td>\n",
       "      <td>2863.43</td>\n",
       "      <td>1642.66</td>\n",
       "      <td>2518.0</td>\n",
       "      <td>952.0</td>\n",
       "      <td>843.0</td>\n",
       "      <td>4313</td>\n",
       "      <td>575520.0</td>\n",
       "      <td>664339.0</td>\n",
       "      <td>644271.0</td>\n",
       "      <td>1884130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>3.88</td>\n",
       "      <td>3.78</td>\n",
       "      <td>3.72</td>\n",
       "      <td>3.82</td>\n",
       "      <td>906.40</td>\n",
       "      <td>3510.44</td>\n",
       "      <td>2165.62</td>\n",
       "      <td>1751.36</td>\n",
       "      <td>520.0</td>\n",
       "      <td>189.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>936</td>\n",
       "      <td>121458.0</td>\n",
       "      <td>175522.0</td>\n",
       "      <td>132103.0</td>\n",
       "      <td>429083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>3.91</td>\n",
       "      <td>3.56</td>\n",
       "      <td>3.82</td>\n",
       "      <td>3.81</td>\n",
       "      <td>909.27</td>\n",
       "      <td>2536.14</td>\n",
       "      <td>2454.65</td>\n",
       "      <td>1592.19</td>\n",
       "      <td>2126.0</td>\n",
       "      <td>743.0</td>\n",
       "      <td>771.0</td>\n",
       "      <td>3640</td>\n",
       "      <td>494643.0</td>\n",
       "      <td>530054.0</td>\n",
       "      <td>495840.0</td>\n",
       "      <td>1520537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>3.95</td>\n",
       "      <td>4.03</td>\n",
       "      <td>4.56</td>\n",
       "      <td>4.08</td>\n",
       "      <td>777.46</td>\n",
       "      <td>2847.24</td>\n",
       "      <td>2969.85</td>\n",
       "      <td>1675.06</td>\n",
       "      <td>522.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>934</td>\n",
       "      <td>102625.0</td>\n",
       "      <td>165140.0</td>\n",
       "      <td>115824.0</td>\n",
       "      <td>383589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>3.58</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.50</td>\n",
       "      <td>3.81</td>\n",
       "      <td>1161.97</td>\n",
       "      <td>1865.45</td>\n",
       "      <td>1757.38</td>\n",
       "      <td>1402.38</td>\n",
       "      <td>118.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>198</td>\n",
       "      <td>38345.0</td>\n",
       "      <td>20520.0</td>\n",
       "      <td>14059.0</td>\n",
       "      <td>72924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>3.85</td>\n",
       "      <td>3.79</td>\n",
       "      <td>3.90</td>\n",
       "      <td>3.84</td>\n",
       "      <td>849.32</td>\n",
       "      <td>2094.08</td>\n",
       "      <td>1772.94</td>\n",
       "      <td>1279.90</td>\n",
       "      <td>743.0</td>\n",
       "      <td>277.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>1207</td>\n",
       "      <td>163919.0</td>\n",
       "      <td>152868.0</td>\n",
       "      <td>85101.0</td>\n",
       "      <td>401888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>3.52</td>\n",
       "      <td>4.79</td>\n",
       "      <td>3.79</td>\n",
       "      <td>3.85</td>\n",
       "      <td>570.98</td>\n",
       "      <td>4460.29</td>\n",
       "      <td>3252.12</td>\n",
       "      <td>1966.48</td>\n",
       "      <td>229.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>435</td>\n",
       "      <td>37114.0</td>\n",
       "      <td>107047.0</td>\n",
       "      <td>78051.0</td>\n",
       "      <td>222212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>3.97</td>\n",
       "      <td>3.62</td>\n",
       "      <td>3.58</td>\n",
       "      <td>3.81</td>\n",
       "      <td>988.68</td>\n",
       "      <td>2736.92</td>\n",
       "      <td>2931.10</td>\n",
       "      <td>1780.28</td>\n",
       "      <td>1143.0</td>\n",
       "      <td>405.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>1924</td>\n",
       "      <td>284739.0</td>\n",
       "      <td>306535.0</td>\n",
       "      <td>307765.0</td>\n",
       "      <td>899039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>3.63</td>\n",
       "      <td>3.52</td>\n",
       "      <td>4.01</td>\n",
       "      <td>3.68</td>\n",
       "      <td>769.21</td>\n",
       "      <td>2319.63</td>\n",
       "      <td>2998.83</td>\n",
       "      <td>1558.40</td>\n",
       "      <td>1259.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>493.0</td>\n",
       "      <td>2199</td>\n",
       "      <td>266916.0</td>\n",
       "      <td>294593.0</td>\n",
       "      <td>368856.0</td>\n",
       "      <td>930365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>3.68</td>\n",
       "      <td>3.53</td>\n",
       "      <td>3.63</td>\n",
       "      <td>3.64</td>\n",
       "      <td>740.97</td>\n",
       "      <td>1963.63</td>\n",
       "      <td>2386.68</td>\n",
       "      <td>1347.28</td>\n",
       "      <td>420.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>149.0</td>\n",
       "      <td>721</td>\n",
       "      <td>84471.0</td>\n",
       "      <td>84436.0</td>\n",
       "      <td>97854.0</td>\n",
       "      <td>266761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>3.31</td>\n",
       "      <td>3.93</td>\n",
       "      <td>3.33</td>\n",
       "      <td>3.47</td>\n",
       "      <td>975.46</td>\n",
       "      <td>2944.64</td>\n",
       "      <td>2723.22</td>\n",
       "      <td>1721.98</td>\n",
       "      <td>116.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>201</td>\n",
       "      <td>34141.0</td>\n",
       "      <td>41225.0</td>\n",
       "      <td>24509.0</td>\n",
       "      <td>99875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>3.67</td>\n",
       "      <td>3.49</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.69</td>\n",
       "      <td>645.00</td>\n",
       "      <td>1736.07</td>\n",
       "      <td>2693.30</td>\n",
       "      <td>1324.81</td>\n",
       "      <td>638.0</td>\n",
       "      <td>237.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>1136</td>\n",
       "      <td>112230.0</td>\n",
       "      <td>118053.0</td>\n",
       "      <td>177758.0</td>\n",
       "      <td>408041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>3.63</td>\n",
       "      <td>3.99</td>\n",
       "      <td>3.90</td>\n",
       "      <td>3.76</td>\n",
       "      <td>755.61</td>\n",
       "      <td>2747.77</td>\n",
       "      <td>2818.93</td>\n",
       "      <td>1603.70</td>\n",
       "      <td>951.0</td>\n",
       "      <td>351.0</td>\n",
       "      <td>390.0</td>\n",
       "      <td>1692</td>\n",
       "      <td>197969.0</td>\n",
       "      <td>241804.0</td>\n",
       "      <td>281893.0</td>\n",
       "      <td>721666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>4.21</td>\n",
       "      <td>4.47</td>\n",
       "      <td>4.63</td>\n",
       "      <td>4.37</td>\n",
       "      <td>857.33</td>\n",
       "      <td>2145.53</td>\n",
       "      <td>2699.41</td>\n",
       "      <td>1602.89</td>\n",
       "      <td>265.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>533</td>\n",
       "      <td>54012.0</td>\n",
       "      <td>68657.0</td>\n",
       "      <td>72884.0</td>\n",
       "      <td>195553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>3.80</td>\n",
       "      <td>3.67</td>\n",
       "      <td>3.44</td>\n",
       "      <td>3.69</td>\n",
       "      <td>747.27</td>\n",
       "      <td>2588.18</td>\n",
       "      <td>3206.23</td>\n",
       "      <td>1732.04</td>\n",
       "      <td>726.0</td>\n",
       "      <td>345.0</td>\n",
       "      <td>251.0</td>\n",
       "      <td>1322</td>\n",
       "      <td>142728.0</td>\n",
       "      <td>243289.0</td>\n",
       "      <td>234055.0</td>\n",
       "      <td>620072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>3.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.00</td>\n",
       "      <td>201.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>201.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>3.95</td>\n",
       "      <td>4.42</td>\n",
       "      <td>4.15</td>\n",
       "      <td>4.13</td>\n",
       "      <td>441.50</td>\n",
       "      <td>2440.42</td>\n",
       "      <td>2155.62</td>\n",
       "      <td>1425.98</td>\n",
       "      <td>87.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>194</td>\n",
       "      <td>9713.0</td>\n",
       "      <td>29285.0</td>\n",
       "      <td>28023.0</td>\n",
       "      <td>67021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>3.74</td>\n",
       "      <td>4.06</td>\n",
       "      <td>3.94</td>\n",
       "      <td>3.86</td>\n",
       "      <td>711.90</td>\n",
       "      <td>2095.19</td>\n",
       "      <td>2188.74</td>\n",
       "      <td>1363.38</td>\n",
       "      <td>1130.0</td>\n",
       "      <td>524.0</td>\n",
       "      <td>488.0</td>\n",
       "      <td>2142</td>\n",
       "      <td>214994.0</td>\n",
       "      <td>270279.0</td>\n",
       "      <td>271404.0</td>\n",
       "      <td>756677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>3.88</td>\n",
       "      <td>3.57</td>\n",
       "      <td>3.98</td>\n",
       "      <td>3.82</td>\n",
       "      <td>628.50</td>\n",
       "      <td>1888.59</td>\n",
       "      <td>2834.00</td>\n",
       "      <td>1383.08</td>\n",
       "      <td>438.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>783</td>\n",
       "      <td>71020.0</td>\n",
       "      <td>96318.0</td>\n",
       "      <td>116194.0</td>\n",
       "      <td>283532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>3.82</td>\n",
       "      <td>4.06</td>\n",
       "      <td>3.26</td>\n",
       "      <td>3.77</td>\n",
       "      <td>993.08</td>\n",
       "      <td>3605.71</td>\n",
       "      <td>2311.33</td>\n",
       "      <td>1880.61</td>\n",
       "      <td>458.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>796</td>\n",
       "      <td>119169.0</td>\n",
       "      <td>187497.0</td>\n",
       "      <td>90142.0</td>\n",
       "      <td>396808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>4.44</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3.83</td>\n",
       "      <td>3.82</td>\n",
       "      <td>1857.56</td>\n",
       "      <td>3703.29</td>\n",
       "      <td>3816.00</td>\n",
       "      <td>2978.95</td>\n",
       "      <td>40.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>84</td>\n",
       "      <td>16718.0</td>\n",
       "      <td>25923.0</td>\n",
       "      <td>22896.0</td>\n",
       "      <td>65537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>3.66</td>\n",
       "      <td>3.92</td>\n",
       "      <td>3.44</td>\n",
       "      <td>3.67</td>\n",
       "      <td>1088.09</td>\n",
       "      <td>2997.01</td>\n",
       "      <td>2557.18</td>\n",
       "      <td>1800.44</td>\n",
       "      <td>1591.0</td>\n",
       "      <td>651.0</td>\n",
       "      <td>506.0</td>\n",
       "      <td>2748</td>\n",
       "      <td>473319.0</td>\n",
       "      <td>497504.0</td>\n",
       "      <td>375905.0</td>\n",
       "      <td>1346728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>3.76</td>\n",
       "      <td>3.78</td>\n",
       "      <td>3.77</td>\n",
       "      <td>3.77</td>\n",
       "      <td>852.53</td>\n",
       "      <td>2552.21</td>\n",
       "      <td>2694.49</td>\n",
       "      <td>1608.89</td>\n",
       "      <td>21389.0</td>\n",
       "      <td>8434.0</td>\n",
       "      <td>7613.0</td>\n",
       "      <td>37436</td>\n",
       "      <td>4844936.0</td>\n",
       "      <td>5696531.0</td>\n",
       "      <td>5442876.0</td>\n",
       "      <td>15984343</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       mean                                                            sum  \\\n",
       "         数量                        销售额                                  数量   \n",
       "类别     办公用品    家具    技术   All     办公用品       家具       技术      All     办公用品   \n",
       "省/自治区                                                                        \n",
       "上海     3.71  4.13  3.62  3.80  1140.97  2663.35  2916.90  1875.71    645.0   \n",
       "云南     3.91  4.22  3.88  3.97   891.67  3554.18  2890.24  1863.79    540.0   \n",
       "内蒙古    3.39  3.36  3.42  3.39   643.98  2168.77  2591.39  1314.83    390.0   \n",
       "北京     3.75  3.70  3.48  3.69  1163.16  2769.72  2391.30  1726.86    465.0   \n",
       "吉林     3.52  3.77  4.08  3.70   726.83  2712.25  3152.66  1701.56   1042.0   \n",
       "四川     3.95  3.25  3.23  3.64   837.54  1447.49  1797.31  1157.88    525.0   \n",
       "天津     3.92  4.21  3.59  3.91   787.44  2576.76  3137.44  1611.21    709.0   \n",
       "宁夏     3.75  4.56  3.25  3.96  1627.42  1827.67  1328.50  1651.68     45.0   \n",
       "安徽     3.71  3.49  3.58  3.63   753.80  2137.63  2790.01  1477.87    987.0   \n",
       "山东     3.78  3.73  3.75  3.76   862.85  2605.25  2863.43  1642.66   2518.0   \n",
       "山西     3.88  3.78  3.72  3.82   906.40  3510.44  2165.62  1751.36    520.0   \n",
       "广东     3.91  3.56  3.82  3.81   909.27  2536.14  2454.65  1592.19   2126.0   \n",
       "广西     3.95  4.03  4.56  4.08   777.46  2847.24  2969.85  1675.06    522.0   \n",
       "新疆     3.58  4.00  4.50  3.81  1161.97  1865.45  1757.38  1402.38    118.0   \n",
       "江苏     3.85  3.79  3.90  3.84   849.32  2094.08  1772.94  1279.90    743.0   \n",
       "江西     3.52  4.79  3.79  3.85   570.98  4460.29  3252.12  1966.48    229.0   \n",
       "河北     3.97  3.62  3.58  3.81   988.68  2736.92  2931.10  1780.28   1143.0   \n",
       "河南     3.63  3.52  4.01  3.68   769.21  2319.63  2998.83  1558.40   1259.0   \n",
       "浙江     3.68  3.53  3.63  3.64   740.97  1963.63  2386.68  1347.28    420.0   \n",
       "海南     3.31  3.93  3.33  3.47   975.46  2944.64  2723.22  1721.98    116.0   \n",
       "湖北     3.67  3.49  3.95  3.69   645.00  1736.07  2693.30  1324.81    638.0   \n",
       "湖南     3.63  3.99  3.90  3.76   755.61  2747.77  2818.93  1603.70    951.0   \n",
       "甘肃     4.21  4.47  4.63  4.37   857.33  2145.53  2699.41  1602.89    265.0   \n",
       "福建     3.80  3.67  3.44  3.69   747.27  2588.18  3206.23  1732.04    726.0   \n",
       "西藏     3.00   NaN   NaN  3.00   201.00      NaN      NaN   201.00      3.0   \n",
       "贵州     3.95  4.42  4.15  4.13   441.50  2440.42  2155.62  1425.98     87.0   \n",
       "辽宁     3.74  4.06  3.94  3.86   711.90  2095.19  2188.74  1363.38   1130.0   \n",
       "重庆     3.88  3.57  3.98  3.82   628.50  1888.59  2834.00  1383.08    438.0   \n",
       "陕西     3.82  4.06  3.26  3.77   993.08  3605.71  2311.33  1880.61    458.0   \n",
       "青海     4.44  3.00  3.83  3.82  1857.56  3703.29  3816.00  2978.95     40.0   \n",
       "黑龙江    3.66  3.92  3.44  3.67  1088.09  2997.01  2557.18  1800.44   1591.0   \n",
       "All    3.76  3.78  3.77  3.77   852.53  2552.21  2694.49  1608.89  21389.0   \n",
       "\n",
       "                                                                         \n",
       "                                    销售额                                  \n",
       "类别         家具      技术    All       办公用品         家具         技术       All  \n",
       "省/自治区                                                                    \n",
       "上海      343.0   217.0   1205   198529.0   221058.0   175014.0    594601  \n",
       "云南      207.0   194.0    941   123051.0   174155.0   144512.0    441718  \n",
       "内蒙古     148.0   106.0    644    74058.0    95426.0    80333.0    249817  \n",
       "北京      170.0   115.0    750   144232.0   127407.0    78913.0    350552  \n",
       "吉林      400.0   510.0   1952   215143.0   287498.0   394083.0    896724  \n",
       "四川      198.0   126.0    849   111393.0    88297.0    70095.0    269785  \n",
       "天津      244.0   219.0   1172   142526.0   149452.0   191384.0    483362  \n",
       "宁夏       41.0    13.0     99    19529.0    16449.0     5314.0     41292  \n",
       "安徽      352.0   344.0   1683   200511.0   215901.0   267841.0    684253  \n",
       "山东      952.0   843.0   4313   575520.0   664339.0   644271.0   1884130  \n",
       "山西      189.0   227.0    936   121458.0   175522.0   132103.0    429083  \n",
       "广东      743.0   771.0   3640   494643.0   530054.0   495840.0   1520537  \n",
       "广西      234.0   178.0    934   102625.0   165140.0   115824.0    383589  \n",
       "新疆       44.0    36.0    198    38345.0    20520.0    14059.0     72924  \n",
       "江苏      277.0   187.0   1207   163919.0   152868.0    85101.0    401888  \n",
       "江西      115.0    91.0    435    37114.0   107047.0    78051.0    222212  \n",
       "河北      405.0   376.0   1924   284739.0   306535.0   307765.0    899039  \n",
       "河南      447.0   493.0   2199   266916.0   294593.0   368856.0    930365  \n",
       "浙江      152.0   149.0    721    84471.0    84436.0    97854.0    266761  \n",
       "海南       55.0    30.0    201    34141.0    41225.0    24509.0     99875  \n",
       "湖北      237.0   261.0   1136   112230.0   118053.0   177758.0    408041  \n",
       "湖南      351.0   390.0   1692   197969.0   241804.0   281893.0    721666  \n",
       "甘肃      143.0   125.0    533    54012.0    68657.0    72884.0    195553  \n",
       "福建      345.0   251.0   1322   142728.0   243289.0   234055.0    620072  \n",
       "西藏        NaN     NaN      3      201.0        NaN        NaN       201  \n",
       "贵州       53.0    54.0    194     9713.0    29285.0    28023.0     67021  \n",
       "辽宁      524.0   488.0   2142   214994.0   270279.0   271404.0    756677  \n",
       "重庆      182.0   163.0    783    71020.0    96318.0   116194.0    283532  \n",
       "陕西      211.0   127.0    796   119169.0   187497.0    90142.0    396808  \n",
       "青海       21.0    23.0     84    16718.0    25923.0    22896.0     65537  \n",
       "黑龙江     651.0   506.0   2748   473319.0   497504.0   375905.0   1346728  \n",
       "All    8434.0  7613.0  37436  4844936.0  5696531.0  5442876.0  15984343  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ndf=pd.pivot_table(df,values=['数量','销售额'],index='省/自治区',columns='类别',aggfunc=['mean','sum'],margins=True).round(2)\n",
    "ndf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9 - 数据透视｜筛选\n",
    "\n",
    "在上一题的基础上，查询 **「类别」** 等于 **「办公用品」** 的详情"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "('mean', '数量', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('mean', '销售额', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '数量', '办公用品')",
         "rawType": "float64",
         "type": "float"
        },
        {
         "name": "('sum', '销售额', '办公用品')",
         "rawType": "float64",
         "type": "float"
        }
       ],
       "ref": "bacd0e18-7db3-4bf6-8861-fd3e137b235d",
       "rows": [
        [
         "上海",
         "3.71",
         "1140.97",
         "645.0",
         "198529.0"
        ],
        [
         "云南",
         "3.91",
         "891.67",
         "540.0",
         "123051.0"
        ],
        [
         "内蒙古",
         "3.39",
         "643.98",
         "390.0",
         "74058.0"
        ],
        [
         "北京",
         "3.75",
         "1163.16",
         "465.0",
         "144232.0"
        ],
        [
         "吉林",
         "3.52",
         "726.83",
         "1042.0",
         "215143.0"
        ],
        [
         "四川",
         "3.95",
         "837.54",
         "525.0",
         "111393.0"
        ],
        [
         "天津",
         "3.92",
         "787.44",
         "709.0",
         "142526.0"
        ],
        [
         "宁夏",
         "3.75",
         "1627.42",
         "45.0",
         "19529.0"
        ],
        [
         "安徽",
         "3.71",
         "753.8",
         "987.0",
         "200511.0"
        ],
        [
         "山东",
         "3.78",
         "862.85",
         "2518.0",
         "575520.0"
        ],
        [
         "山西",
         "3.88",
         "906.4",
         "520.0",
         "121458.0"
        ],
        [
         "广东",
         "3.91",
         "909.27",
         "2126.0",
         "494643.0"
        ],
        [
         "广西",
         "3.95",
         "777.46",
         "522.0",
         "102625.0"
        ],
        [
         "新疆",
         "3.58",
         "1161.97",
         "118.0",
         "38345.0"
        ],
        [
         "江苏",
         "3.85",
         "849.32",
         "743.0",
         "163919.0"
        ],
        [
         "江西",
         "3.52",
         "570.98",
         "229.0",
         "37114.0"
        ],
        [
         "河北",
         "3.97",
         "988.68",
         "1143.0",
         "284739.0"
        ],
        [
         "河南",
         "3.63",
         "769.21",
         "1259.0",
         "266916.0"
        ],
        [
         "浙江",
         "3.68",
         "740.97",
         "420.0",
         "84471.0"
        ],
        [
         "海南",
         "3.31",
         "975.46",
         "116.0",
         "34141.0"
        ],
        [
         "湖北",
         "3.67",
         "645.0",
         "638.0",
         "112230.0"
        ],
        [
         "湖南",
         "3.63",
         "755.61",
         "951.0",
         "197969.0"
        ],
        [
         "甘肃",
         "4.21",
         "857.33",
         "265.0",
         "54012.0"
        ],
        [
         "福建",
         "3.8",
         "747.27",
         "726.0",
         "142728.0"
        ],
        [
         "西藏",
         "3.0",
         "201.0",
         "3.0",
         "201.0"
        ],
        [
         "贵州",
         "3.95",
         "441.5",
         "87.0",
         "9713.0"
        ],
        [
         "辽宁",
         "3.74",
         "711.9",
         "1130.0",
         "214994.0"
        ],
        [
         "重庆",
         "3.88",
         "628.5",
         "438.0",
         "71020.0"
        ],
        [
         "陕西",
         "3.82",
         "993.08",
         "458.0",
         "119169.0"
        ],
        [
         "青海",
         "4.44",
         "1857.56",
         "40.0",
         "16718.0"
        ],
        [
         "黑龙江",
         "3.66",
         "1088.09",
         "1591.0",
         "473319.0"
        ],
        [
         "All",
         "3.76",
         "852.53",
         "21389.0",
         "4844936.0"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 32
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>数量</th>\n",
       "      <th>销售额</th>\n",
       "      <th>数量</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>类别</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>办公用品</th>\n",
       "      <th>办公用品</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>省/自治区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>上海</th>\n",
       "      <td>3.71</td>\n",
       "      <td>1140.97</td>\n",
       "      <td>645.0</td>\n",
       "      <td>198529.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>云南</th>\n",
       "      <td>3.91</td>\n",
       "      <td>891.67</td>\n",
       "      <td>540.0</td>\n",
       "      <td>123051.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>内蒙古</th>\n",
       "      <td>3.39</td>\n",
       "      <td>643.98</td>\n",
       "      <td>390.0</td>\n",
       "      <td>74058.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京</th>\n",
       "      <td>3.75</td>\n",
       "      <td>1163.16</td>\n",
       "      <td>465.0</td>\n",
       "      <td>144232.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>吉林</th>\n",
       "      <td>3.52</td>\n",
       "      <td>726.83</td>\n",
       "      <td>1042.0</td>\n",
       "      <td>215143.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>四川</th>\n",
       "      <td>3.95</td>\n",
       "      <td>837.54</td>\n",
       "      <td>525.0</td>\n",
       "      <td>111393.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天津</th>\n",
       "      <td>3.92</td>\n",
       "      <td>787.44</td>\n",
       "      <td>709.0</td>\n",
       "      <td>142526.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>宁夏</th>\n",
       "      <td>3.75</td>\n",
       "      <td>1627.42</td>\n",
       "      <td>45.0</td>\n",
       "      <td>19529.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>安徽</th>\n",
       "      <td>3.71</td>\n",
       "      <td>753.80</td>\n",
       "      <td>987.0</td>\n",
       "      <td>200511.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山东</th>\n",
       "      <td>3.78</td>\n",
       "      <td>862.85</td>\n",
       "      <td>2518.0</td>\n",
       "      <td>575520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>山西</th>\n",
       "      <td>3.88</td>\n",
       "      <td>906.40</td>\n",
       "      <td>520.0</td>\n",
       "      <td>121458.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广东</th>\n",
       "      <td>3.91</td>\n",
       "      <td>909.27</td>\n",
       "      <td>2126.0</td>\n",
       "      <td>494643.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>广西</th>\n",
       "      <td>3.95</td>\n",
       "      <td>777.46</td>\n",
       "      <td>522.0</td>\n",
       "      <td>102625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆</th>\n",
       "      <td>3.58</td>\n",
       "      <td>1161.97</td>\n",
       "      <td>118.0</td>\n",
       "      <td>38345.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江苏</th>\n",
       "      <td>3.85</td>\n",
       "      <td>849.32</td>\n",
       "      <td>743.0</td>\n",
       "      <td>163919.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>江西</th>\n",
       "      <td>3.52</td>\n",
       "      <td>570.98</td>\n",
       "      <td>229.0</td>\n",
       "      <td>37114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北</th>\n",
       "      <td>3.97</td>\n",
       "      <td>988.68</td>\n",
       "      <td>1143.0</td>\n",
       "      <td>284739.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河南</th>\n",
       "      <td>3.63</td>\n",
       "      <td>769.21</td>\n",
       "      <td>1259.0</td>\n",
       "      <td>266916.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>浙江</th>\n",
       "      <td>3.68</td>\n",
       "      <td>740.97</td>\n",
       "      <td>420.0</td>\n",
       "      <td>84471.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>海南</th>\n",
       "      <td>3.31</td>\n",
       "      <td>975.46</td>\n",
       "      <td>116.0</td>\n",
       "      <td>34141.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖北</th>\n",
       "      <td>3.67</td>\n",
       "      <td>645.00</td>\n",
       "      <td>638.0</td>\n",
       "      <td>112230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>湖南</th>\n",
       "      <td>3.63</td>\n",
       "      <td>755.61</td>\n",
       "      <td>951.0</td>\n",
       "      <td>197969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>甘肃</th>\n",
       "      <td>4.21</td>\n",
       "      <td>857.33</td>\n",
       "      <td>265.0</td>\n",
       "      <td>54012.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>福建</th>\n",
       "      <td>3.80</td>\n",
       "      <td>747.27</td>\n",
       "      <td>726.0</td>\n",
       "      <td>142728.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>西藏</th>\n",
       "      <td>3.00</td>\n",
       "      <td>201.00</td>\n",
       "      <td>3.0</td>\n",
       "      <td>201.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>贵州</th>\n",
       "      <td>3.95</td>\n",
       "      <td>441.50</td>\n",
       "      <td>87.0</td>\n",
       "      <td>9713.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁</th>\n",
       "      <td>3.74</td>\n",
       "      <td>711.90</td>\n",
       "      <td>1130.0</td>\n",
       "      <td>214994.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>重庆</th>\n",
       "      <td>3.88</td>\n",
       "      <td>628.50</td>\n",
       "      <td>438.0</td>\n",
       "      <td>71020.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>陕西</th>\n",
       "      <td>3.82</td>\n",
       "      <td>993.08</td>\n",
       "      <td>458.0</td>\n",
       "      <td>119169.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>青海</th>\n",
       "      <td>4.44</td>\n",
       "      <td>1857.56</td>\n",
       "      <td>40.0</td>\n",
       "      <td>16718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江</th>\n",
       "      <td>3.66</td>\n",
       "      <td>1088.09</td>\n",
       "      <td>1591.0</td>\n",
       "      <td>473319.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>3.76</td>\n",
       "      <td>852.53</td>\n",
       "      <td>21389.0</td>\n",
       "      <td>4844936.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       mean               sum           \n",
       "         数量      销售额       数量        销售额\n",
       "类别     办公用品     办公用品     办公用品       办公用品\n",
       "省/自治区                                   \n",
       "上海     3.71  1140.97    645.0   198529.0\n",
       "云南     3.91   891.67    540.0   123051.0\n",
       "内蒙古    3.39   643.98    390.0    74058.0\n",
       "北京     3.75  1163.16    465.0   144232.0\n",
       "吉林     3.52   726.83   1042.0   215143.0\n",
       "四川     3.95   837.54    525.0   111393.0\n",
       "天津     3.92   787.44    709.0   142526.0\n",
       "宁夏     3.75  1627.42     45.0    19529.0\n",
       "安徽     3.71   753.80    987.0   200511.0\n",
       "山东     3.78   862.85   2518.0   575520.0\n",
       "山西     3.88   906.40    520.0   121458.0\n",
       "广东     3.91   909.27   2126.0   494643.0\n",
       "广西     3.95   777.46    522.0   102625.0\n",
       "新疆     3.58  1161.97    118.0    38345.0\n",
       "江苏     3.85   849.32    743.0   163919.0\n",
       "江西     3.52   570.98    229.0    37114.0\n",
       "河北     3.97   988.68   1143.0   284739.0\n",
       "河南     3.63   769.21   1259.0   266916.0\n",
       "浙江     3.68   740.97    420.0    84471.0\n",
       "海南     3.31   975.46    116.0    34141.0\n",
       "湖北     3.67   645.00    638.0   112230.0\n",
       "湖南     3.63   755.61    951.0   197969.0\n",
       "甘肃     4.21   857.33    265.0    54012.0\n",
       "福建     3.80   747.27    726.0   142728.0\n",
       "西藏     3.00   201.00      3.0      201.0\n",
       "贵州     3.95   441.50     87.0     9713.0\n",
       "辽宁     3.74   711.90   1130.0   214994.0\n",
       "重庆     3.88   628.50    438.0    71020.0\n",
       "陕西     3.82   993.08    458.0   119169.0\n",
       "青海     4.44  1857.56     40.0    16718.0\n",
       "黑龙江    3.66  1088.09   1591.0   473319.0\n",
       "All    3.76   852.53  21389.0  4844936.0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx=pd.IndexSlice\n",
    "\n",
    "ndf.loc[:,idx[:,:,'办公用品']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10 -数据透视｜逆透视"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "逆透视就是将宽的表转换为长的表，例如将第 5 题的透视表进行逆透视，其中不需要转换的列为『数量』列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "省/自治区",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "指标",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "总额",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "2f3d57ca-24f5-421a-baf1-f33a8e254e68",
       "rows": [
        [
         "0",
         "湖南",
         "销售总额",
         "721666"
        ],
        [
         "1",
         "福建",
         "销售总额",
         "620072"
        ],
        [
         "2",
         "辽宁",
         "销售总额",
         "756677"
        ],
        [
         "3",
         "广西",
         "销售总额",
         "383589"
        ],
        [
         "4",
         "北京",
         "销售总额",
         "350552"
        ],
        [
         "5",
         "云南",
         "销售总额",
         "441718"
        ],
        [
         "6",
         "河北",
         "销售总额",
         "899039"
        ],
        [
         "7",
         "黑龙江",
         "销售总额",
         "1346728"
        ],
        [
         "8",
         "吉林",
         "销售总额",
         "896724"
        ],
        [
         "9",
         "广东",
         "销售总额",
         "1520537"
        ],
        [
         "10",
         "山东",
         "销售总额",
         "1884130"
        ],
        [
         "11",
         "上海",
         "销售总额",
         "594601"
        ],
        [
         "12",
         "天津",
         "销售总额",
         "483362"
        ],
        [
         "13",
         "陕西",
         "销售总额",
         "396808"
        ],
        [
         "14",
         "山西",
         "销售总额",
         "429083"
        ],
        [
         "15",
         "河南",
         "销售总额",
         "930365"
        ],
        [
         "16",
         "安徽",
         "销售总额",
         "684253"
        ],
        [
         "17",
         "重庆",
         "销售总额",
         "283532"
        ],
        [
         "18",
         "湖北",
         "销售总额",
         "408041"
        ],
        [
         "19",
         "江苏",
         "销售总额",
         "401888"
        ],
        [
         "20",
         "内蒙古",
         "销售总额",
         "249817"
        ],
        [
         "21",
         "四川",
         "销售总额",
         "269785"
        ],
        [
         "22",
         "甘肃",
         "销售总额",
         "195553"
        ],
        [
         "23",
         "浙江",
         "销售总额",
         "266761"
        ],
        [
         "24",
         "海南",
         "销售总额",
         "99875"
        ],
        [
         "25",
         "贵州",
         "销售总额",
         "67021"
        ],
        [
         "26",
         "江西",
         "销售总额",
         "222212"
        ],
        [
         "27",
         "新疆",
         "销售总额",
         "72924"
        ],
        [
         "28",
         "宁夏",
         "销售总额",
         "41292"
        ],
        [
         "29",
         "西藏",
         "销售总额",
         "201"
        ],
        [
         "30",
         "青海",
         "销售总额",
         "65537"
        ],
        [
         "31",
         "湖南",
         "利润总额",
         "92944"
        ],
        [
         "32",
         "福建",
         "利润总额",
         "133791"
        ],
        [
         "33",
         "辽宁",
         "利润总额",
         "-24930"
        ],
        [
         "34",
         "广西",
         "利润总额",
         "61444"
        ],
        [
         "35",
         "北京",
         "利润总额",
         "57883"
        ],
        [
         "36",
         "云南",
         "利润总额",
         "83201"
        ],
        [
         "37",
         "河北",
         "利润总额",
         "153247"
        ],
        [
         "38",
         "黑龙江",
         "利润总额",
         "228262"
        ],
        [
         "39",
         "吉林",
         "利润总额",
         "152504"
        ],
        [
         "40",
         "广东",
         "利润总额",
         "278591"
        ],
        [
         "41",
         "山东",
         "利润总额",
         "310042"
        ],
        [
         "42",
         "上海",
         "利润总额",
         "87236"
        ],
        [
         "43",
         "天津",
         "利润总额",
         "63108"
        ],
        [
         "44",
         "陕西",
         "利润总额",
         "61753"
        ],
        [
         "45",
         "山西",
         "利润总额",
         "79913"
        ],
        [
         "46",
         "河南",
         "利润总额",
         "168714"
        ],
        [
         "47",
         "安徽",
         "利润总额",
         "133312"
        ],
        [
         "48",
         "重庆",
         "利润总额",
         "39688"
        ],
        [
         "49",
         "湖北",
         "利润总额",
         "-22896"
        ]
       ],
       "shape": {
        "columns": 3,
        "rows": 62
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>省/自治区</th>\n",
       "      <th>指标</th>\n",
       "      <th>总额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>湖南</td>\n",
       "      <td>销售总额</td>\n",
       "      <td>721666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>福建</td>\n",
       "      <td>销售总额</td>\n",
       "      <td>620072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>辽宁</td>\n",
       "      <td>销售总额</td>\n",
       "      <td>756677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>广西</td>\n",
       "      <td>销售总额</td>\n",
       "      <td>383589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北京</td>\n",
       "      <td>销售总额</td>\n",
       "      <td>350552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>江西</td>\n",
       "      <td>利润总额</td>\n",
       "      <td>27144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>新疆</td>\n",
       "      <td>利润总额</td>\n",
       "      <td>13696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>宁夏</td>\n",
       "      <td>利润总额</td>\n",
       "      <td>-1149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>西藏</td>\n",
       "      <td>利润总额</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>青海</td>\n",
       "      <td>利润总额</td>\n",
       "      <td>4354</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   省/自治区    指标      总额\n",
       "0     湖南  销售总额  721666\n",
       "1     福建  销售总额  620072\n",
       "2     辽宁  销售总额  756677\n",
       "3     广西  销售总额  383589\n",
       "4     北京  销售总额  350552\n",
       "..   ...   ...     ...\n",
       "57    江西  利润总额   27144\n",
       "58    新疆  利润总额   13696\n",
       "59    宁夏  利润总额   -1149\n",
       "60    西藏  利润总额       4\n",
       "61    青海  利润总额    4354\n",
       "\n",
       "[62 rows x 3 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 首先重置索引，将'省/自治区'从索引变为普通列\n",
    "ndf_reset = ndf.reset_index()\n",
    "\n",
    "# 使用melt函数进行逆透视\n",
    "# id_vars: 指定保留的标识列（不被转换的列），这里是'省/自治区'\n",
    "# value_vars: 若不指定，则默认转换除id_vars外的所有列（即'销售总额'和'利润总额'）\n",
    "# var_name: 为新列命名，用于存放原来的列名（即'销售总额'和'利润总额'这两个标签）\n",
    "# value_name: 为新列命名，用于存放原来列下的数值\n",
    "ndf_long = ndf_reset.melt(\n",
    "    id_vars='省/自治区', \n",
    "    var_name='指标', \n",
    "    value_name='总额'\n",
    ")\n",
    "\n",
    "# print(ndf_long.head())\n",
    "ndf_long"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`concat`函数的核心语法如下：\n",
    "```python\n",
    "pandas.concat(objs, axis=0, join='outer', ignore_index=False, keys=None, levels=None, names=None, verify_integrity=False, sort=False, copy=True)\n",
    "```\n",
    "\n",
    "下表详细说明了每个参数的用途和常见取值，你可以根据实际的数据合并需求进行选择和组合。\n",
    "\n",
    "| **参数名** | **说明** | **常用取值与效果** |\n",
    "| :--- | :--- | :--- |\n",
    "| **`objs`** | **必填**，需要连接的对象序列。 | 通常是一个由Series或DataFrame组成的列表或字典，如 `[df1, df2, df3]`。 |\n",
    "| **`axis`** | 指定连接的轴向。 | - `0` 或 `'index'` (默认): 按行连接（纵向堆叠）。<br>- `1` 或 `'columns'`: 按列连接（横向堆叠）。 |\n",
    "| **`join`** | 指定如何处理非连接轴上的索引（即当DataFrame的列名不完全相同时如何处理）。 | - `'outer'` (默认): 取索引的并集。不存在的值用`NaN`填充。<br>- `'inner'`: 取索引的交集。只保留共有的部分。 |\n",
    "| **`ignore_index`** | 是否忽略原对象的索引。 | - `False` (默认): 保留原索引。<br>- `True`: 忽略原索引，生成新的连续整数索引（0, ..., n-1）。在**垂直合并**时重置索引非常有用。 |\n",
    "| **`keys`** | 为连接后的数据添加外层索引，形成层次化索引（MultiIndex），便于识别数据来源。 | 传入一个序列，如 `keys=['x', 'y']`，合并后的数据可以通过 `result.loc['x']` 来提取原始`df1`的数据。 |\n",
    "| **`verify_integrity`** | 检查连接轴（而非行索引）上是否有重复项。 | - `False` (默认): 不检查。<br>- `True`: 如果发现重复项，会抛出 `ValueError` 异常。可用于数据清洗时的重复项检查。 |\n",
    "| **`sort`** | 是否对非连接轴上的索引进行排序。 | - `False` (默认): 保留列的原始顺序。<br>- `True`: 对合并后的列名按字典序排序。 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### concat - 数据拼接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`concat`主要用于**数据拼接**，也是非常常用的一个操作\n",
    "\n",
    "除了官方文档外很难找到比官方文档更好的练习\n",
    "\n",
    "以下案例来源或基于 [👉官方文档](https://pandas.pydata.org/pandas-docs/version/0.20/merging.html) 中的案例修改而来\n",
    "\n",
    "在练习之前应执行下方代码生成数据\n",
    "\n",
    "并应预览不同数据的结构以及每题的图解（如果有）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                    'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "                    'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                    'D': ['D0', 'D1', 'D2', 'D3']},\n",
    "                   index=[0, 1, 2, 3])\n",
    "\n",
    "\n",
    "df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],\n",
    "                    'B': ['B4', 'B5', 'B6', 'B7'],\n",
    "                    'C': ['C4', 'C5', 'C6', 'C7'],\n",
    "                    'D': ['D4', 'D5', 'D6', 'D7']},\n",
    "                   index=[4, 5, 6, 7])\n",
    "\n",
    "\n",
    "df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],\n",
    "                    'B': ['B8', 'B9', 'B10', 'B11'],\n",
    "                    'C': ['C8', 'C9', 'C10', 'C11'],\n",
    "                    'D': ['D8', 'D9', 'D10', 'D11']},\n",
    "                   index=[8, 9, 10, 11])\n",
    "\n",
    "\n",
    "df4 = pd.DataFrame({'B': ['B2', 'B3', 'B6', 'B7'],\n",
    "                    'D': ['D2', 'D3', 'D6', 'D7'],\n",
    "                    'F': ['F2', 'F3', 'F6', 'F7']},\n",
    "                   index=[2, 3, 6, 7])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "17d87ba4-f4a7-4261-8963-236695962d5d",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "A1",
         "B1",
         "C1",
         "D1"
        ],
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D\n",
       "0  A0  B0  C0  D0\n",
       "1  A1  B1  C1  D1\n",
       "2  A2  B2  C2  D2\n",
       "3  A3  B3  C3  D3"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "815a326b-e95b-4c24-8762-57e5d562d674",
       "rows": [
        [
         "4",
         "A4",
         "B4",
         "C4",
         "D4"
        ],
        [
         "5",
         "A5",
         "B5",
         "C5",
         "D5"
        ],
        [
         "6",
         "A6",
         "B6",
         "C6",
         "D6"
        ],
        [
         "7",
         "A7",
         "B7",
         "C7",
         "D7"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>C4</td>\n",
       "      <td>D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>C5</td>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>B6</td>\n",
       "      <td>C6</td>\n",
       "      <td>D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A7</td>\n",
       "      <td>B7</td>\n",
       "      <td>C7</td>\n",
       "      <td>D7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D\n",
       "4  A4  B4  C4  D4\n",
       "5  A5  B5  C5  D5\n",
       "6  A6  B6  C6  D6\n",
       "7  A7  B7  C7  D7"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "6afae8ca-d9c5-4beb-b573-fb2e26bd70a1",
       "rows": [
        [
         "8",
         "A8",
         "B8",
         "C8",
         "D8"
        ],
        [
         "9",
         "A9",
         "B9",
         "C9",
         "D9"
        ],
        [
         "10",
         "A10",
         "B10",
         "C10",
         "D10"
        ],
        [
         "11",
         "A11",
         "B11",
         "C11",
         "D11"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A8</td>\n",
       "      <td>B8</td>\n",
       "      <td>C8</td>\n",
       "      <td>D8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A9</td>\n",
       "      <td>B9</td>\n",
       "      <td>C9</td>\n",
       "      <td>D9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A10</td>\n",
       "      <td>B10</td>\n",
       "      <td>C10</td>\n",
       "      <td>D10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A11</td>\n",
       "      <td>B11</td>\n",
       "      <td>C11</td>\n",
       "      <td>D11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A    B    C    D\n",
       "8    A8   B8   C8   D8\n",
       "9    A9   B9   C9   D9\n",
       "10  A10  B10  C10  D10\n",
       "11  A11  B11  C11  D11"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "F",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "390a1d19-b4bd-4e79-8d88-ba41a7dd6469",
       "rows": [
        [
         "2",
         "B2",
         "D2",
         "F2"
        ],
        [
         "3",
         "B3",
         "D3",
         "F3"
        ],
        [
         "6",
         "B6",
         "D6",
         "F6"
        ],
        [
         "7",
         "B7",
         "D7",
         "F7"
        ]
       ],
       "shape": {
        "columns": 3,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B6</td>\n",
       "      <td>D6</td>\n",
       "      <td>F6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B7</td>\n",
       "      <td>D7</td>\n",
       "      <td>F7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    B   D   F\n",
       "2  B2  D2  F2\n",
       "3  B3  D3  F3\n",
       "6  B6  D6  F6\n",
       "7  B7  D7  F7"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 11 - <font color = '#FB8E00'>concat</font>｜默认拼接\n",
    "\n",
    "拼接 df1 和 df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "fd67d584-682b-44ee-bde5-745b4de45777",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "A1",
         "B1",
         "C1",
         "D1"
        ],
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3"
        ],
        [
         "4",
         "A4",
         "B4",
         "C4",
         "D4"
        ],
        [
         "5",
         "A5",
         "B5",
         "C5",
         "D5"
        ],
        [
         "6",
         "A6",
         "B6",
         "C6",
         "D6"
        ],
        [
         "7",
         "A7",
         "B7",
         "C7",
         "D7"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 8
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>C4</td>\n",
       "      <td>D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>C5</td>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>B6</td>\n",
       "      <td>C6</td>\n",
       "      <td>D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A7</td>\n",
       "      <td>B7</td>\n",
       "      <td>C7</td>\n",
       "      <td>D7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D\n",
       "0  A0  B0  C0  D0\n",
       "1  A1  B1  C1  D1\n",
       "2  A2  B2  C2  D2\n",
       "3  A3  B3  C3  D3\n",
       "4  A4  B4  C4  D4\n",
       "5  A5  B5  C5  D5\n",
       "6  A6  B6  C6  D6\n",
       "7  A7  B7  C7  D7"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 12 - <font color = '#FB8E00'>concat</font>｜拼接多个\n",
    "\n",
    "垂直拼接 `df1、df2、df3`，效果如下图所示\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_concat_basic.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "b6180f69-73b8-461f-ac02-0848d38b2640",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "A1",
         "B1",
         "C1",
         "D1"
        ],
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3"
        ],
        [
         "4",
         "A4",
         "B4",
         "C4",
         "D4"
        ],
        [
         "5",
         "A5",
         "B5",
         "C5",
         "D5"
        ],
        [
         "6",
         "A6",
         "B6",
         "C6",
         "D6"
        ],
        [
         "7",
         "A7",
         "B7",
         "C7",
         "D7"
        ],
        [
         "8",
         "A8",
         "B8",
         "C8",
         "D8"
        ],
        [
         "9",
         "A9",
         "B9",
         "C9",
         "D9"
        ],
        [
         "10",
         "A10",
         "B10",
         "C10",
         "D10"
        ],
        [
         "11",
         "A11",
         "B11",
         "C11",
         "D11"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 12
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>C4</td>\n",
       "      <td>D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>C5</td>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>B6</td>\n",
       "      <td>C6</td>\n",
       "      <td>D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A7</td>\n",
       "      <td>B7</td>\n",
       "      <td>C7</td>\n",
       "      <td>D7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A8</td>\n",
       "      <td>B8</td>\n",
       "      <td>C8</td>\n",
       "      <td>D8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A9</td>\n",
       "      <td>B9</td>\n",
       "      <td>C9</td>\n",
       "      <td>D9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A10</td>\n",
       "      <td>B10</td>\n",
       "      <td>C10</td>\n",
       "      <td>D10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A11</td>\n",
       "      <td>B11</td>\n",
       "      <td>C11</td>\n",
       "      <td>D11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A    B    C    D\n",
       "0    A0   B0   C0   D0\n",
       "1    A1   B1   C1   D1\n",
       "2    A2   B2   C2   D2\n",
       "3    A3   B3   C3   D3\n",
       "4    A4   B4   C4   D4\n",
       "5    A5   B5   C5   D5\n",
       "6    A6   B6   C6   D6\n",
       "7    A7   B7   C7   D7\n",
       "8    A8   B8   C8   D8\n",
       "9    A9   B9   C9   D9\n",
       "10  A10  B10  C10  D10\n",
       "11  A11  B11  C11  D11"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2,df3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 13 - <font color = '#FB8E00'>concat</font>｜重置索引\n",
    "\n",
    "垂直拼接 df1 和 df4，并按顺序重新生成索引，\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_concat_ignore_index.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "F",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "45a5f83d-50f8-4ee0-aa69-19af340347cc",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "C0",
         "D0",
         null
        ],
        [
         "1",
         "A1",
         "B1",
         "C1",
         "D1",
         null
        ],
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2",
         null
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3",
         null
        ],
        [
         "2",
         null,
         "B2",
         null,
         "D2",
         "F2"
        ],
        [
         "3",
         null,
         "B3",
         null,
         "D3",
         "F3"
        ],
        [
         "6",
         null,
         "B6",
         null,
         "D6",
         "F6"
        ],
        [
         "7",
         null,
         "B7",
         null,
         "D7",
         "F7"
        ]
       ],
       "shape": {
        "columns": 5,
        "rows": 8
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D6</td>\n",
       "      <td>F6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D7</td>\n",
       "      <td>F7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B    C   D    F\n",
       "0   A0  B0   C0  D0  NaN\n",
       "1   A1  B1   C1  D1  NaN\n",
       "2   A2  B2   C2  D2  NaN\n",
       "3   A3  B3   C3  D3  NaN\n",
       "2  NaN  B2  NaN  D2   F2\n",
       "3  NaN  B3  NaN  D3   F3\n",
       "6  NaN  B6  NaN  D6   F6\n",
       "7  NaN  B7  NaN  D7   F7"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df4]).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 14 - <font color = '#FB8E00'>concat</font>｜横向拼接\n",
    "\n",
    "横向拼接 `df1、df4`，效果如下图所示\n",
    "\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_concat_axis1.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "F",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "899de8a9-ba0b-4cf7-a1c8-51b74899e4b3",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "C0",
         "D0",
         null,
         null,
         null
        ],
        [
         "1",
         "A1",
         "B1",
         "C1",
         "D1",
         null,
         null,
         null
        ],
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2",
         "B2",
         "D2",
         "F2"
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3",
         "B3",
         "D3",
         "F3"
        ],
        [
         "6",
         null,
         null,
         null,
         null,
         "B6",
         "D6",
         "F6"
        ],
        [
         "7",
         null,
         null,
         null,
         null,
         "B7",
         "D7",
         "F7"
        ]
       ],
       "shape": {
        "columns": 7,
        "rows": 6
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B6</td>\n",
       "      <td>D6</td>\n",
       "      <td>F6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>B7</td>\n",
       "      <td>D7</td>\n",
       "      <td>F7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B    C    D    B    D    F\n",
       "0   A0   B0   C0   D0  NaN  NaN  NaN\n",
       "1   A1   B1   C1   D1  NaN  NaN  NaN\n",
       "2   A2   B2   C2   D2   B2   D2   F2\n",
       "3   A3   B3   C3   D3   B3   D3   F3\n",
       "6  NaN  NaN  NaN  NaN   B6   D6   F6\n",
       "7  NaN  NaN  NaN  NaN   B7   D7   F7"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df4],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![公众号：早起Python](http://liuzaoqi.oss-cn-beijing.aliyuncs.com/2021/09/18/16319660121648.jpg?域名/sample.jpg?x-oss-process=style/stylename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 15 - <font color = '#FB8E00'>concat</font>｜横向拼接（取交集）\n",
    "\n",
    "在上一题的基础上，只取结果的交集\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_concat_axis1_inner.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "F",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "b20e84c8-4ea0-407b-ad17-5978537157f9",
       "rows": [
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2",
         "B2",
         "D2",
         "F2"
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3",
         "B3",
         "D3",
         "F3"
        ]
       ],
       "shape": {
        "columns": 7,
        "rows": 2
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D   B   D   F\n",
       "2  A2  B2  C2  D2  B2  D2  F2\n",
       "3  A3  B3  C3  D3  B3  D3  F3"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df4],axis=1,join='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "微信搜索公众号「早起Python」，关注后可以获得更多资源！"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 16 - <font color = '#FB8E00'>concat</font>｜横向拼接（取指定）\n",
    "\n",
    "在 14 题基础上，只取包含 df1 索引的部分\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_concat_axis1_join_axes.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "F",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "ff67a569-e878-4ee6-8033-3f6d04c2ea1c",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "C0",
         "D0",
         null,
         null,
         null
        ],
        [
         "1",
         "A1",
         "B1",
         "C1",
         "D1",
         null,
         null,
         null
        ],
        [
         "2",
         "A2",
         "B2",
         "C2",
         "D2",
         "B2",
         "D2",
         "F2"
        ],
        [
         "3",
         "A3",
         "B3",
         "C3",
         "D3",
         "B3",
         "D3",
         "F3"
        ]
       ],
       "shape": {
        "columns": 7,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "      <td>B2</td>\n",
       "      <td>D2</td>\n",
       "      <td>F2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "      <td>B3</td>\n",
       "      <td>D3</td>\n",
       "      <td>F3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B   C   D    B    D    F\n",
       "0  A0  B0  C0  D0  NaN  NaN  NaN\n",
       "1  A1  B1  C1  D1  NaN  NaN  NaN\n",
       "2  A2  B2  C2  D2   B2   D2   F2\n",
       "3  A3  B3  C3  D3   B3   D3   F3"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df4.reindex(df1.index)],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 17 - <font color = '#FB8E00'>concat</font>｜新增索引\n",
    "\n",
    "拼接 `df1、df2、df3`，同时新增一个索引（`x、y、z`）来区分不同的表数据来源\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_concat_keys.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "(None, None)",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "1997b7bb-38bc-4b6e-8f90-f767b18568de",
       "rows": [
        [
         "('x', 0)",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "('x', 1)",
         "A1",
         "B1",
         "C1",
         "D1"
        ],
        [
         "('x', 2)",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "('x', 3)",
         "A3",
         "B3",
         "C3",
         "D3"
        ],
        [
         "('y', 4)",
         "A4",
         "B4",
         "C4",
         "D4"
        ],
        [
         "('y', 5)",
         "A5",
         "B5",
         "C5",
         "D5"
        ],
        [
         "('y', 6)",
         "A6",
         "B6",
         "C6",
         "D6"
        ],
        [
         "('y', 7)",
         "A7",
         "B7",
         "C7",
         "D7"
        ],
        [
         "('z', 8)",
         "A8",
         "B8",
         "C8",
         "D8"
        ],
        [
         "('z', 9)",
         "A9",
         "B9",
         "C9",
         "D9"
        ],
        [
         "('z', 10)",
         "A10",
         "B10",
         "C10",
         "D10"
        ],
        [
         "('z', 11)",
         "A11",
         "B11",
         "C11",
         "D11"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 12
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">x</th>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">y</th>\n",
       "      <th>4</th>\n",
       "      <td>A4</td>\n",
       "      <td>B4</td>\n",
       "      <td>C4</td>\n",
       "      <td>D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A5</td>\n",
       "      <td>B5</td>\n",
       "      <td>C5</td>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A6</td>\n",
       "      <td>B6</td>\n",
       "      <td>C6</td>\n",
       "      <td>D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A7</td>\n",
       "      <td>B7</td>\n",
       "      <td>C7</td>\n",
       "      <td>D7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">z</th>\n",
       "      <th>8</th>\n",
       "      <td>A8</td>\n",
       "      <td>B8</td>\n",
       "      <td>C8</td>\n",
       "      <td>D8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A9</td>\n",
       "      <td>B9</td>\n",
       "      <td>C9</td>\n",
       "      <td>D9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A10</td>\n",
       "      <td>B10</td>\n",
       "      <td>C10</td>\n",
       "      <td>D10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A11</td>\n",
       "      <td>B11</td>\n",
       "      <td>C11</td>\n",
       "      <td>D11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        A    B    C    D\n",
       "x 0    A0   B0   C0   D0\n",
       "  1    A1   B1   C1   D1\n",
       "  2    A2   B2   C2   D2\n",
       "  3    A3   B3   C3   D3\n",
       "y 4    A4   B4   C4   D4\n",
       "  5    A5   B5   C5   D5\n",
       "  6    A6   B6   C6   D6\n",
       "  7    A7   B7   C7   D7\n",
       "z 8    A8   B8   C8   D8\n",
       "  9    A9   B9   C9   D9\n",
       "  10  A10  B10  C10  D10\n",
       "  11  A11  B11  C11  D11"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2,df3],keys=['x','y','z'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### merge - 数据连接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "concat是拼接，merge则是连接，同样重要的一个操作\n",
    "\n",
    "同样很难找到比官方文档更好的练习，以下案例来源或基于 [👉官方文档](https://pandas.pydata.org/pandas-docs/version/0.20/merging.html#database-style-dataframe-joining-merging) 中的案例修改而来\n",
    "\n",
    "在练习之前应执行每题下方的代码生成数据\n",
    "\n",
    "并应预览不同数据的结构以及每题的图解（如果有）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`merge`函数的基本语法如下：\n",
    "```python\n",
    "pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None, \n",
    "         left_index=False, right_index=False, sort=False, \n",
    "         suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)\n",
    "```\n",
    "\n",
    "为了帮你快速建立整体印象，下面这个表格汇总了最核心的参数及其用法。\n",
    "\n",
    "| 参数 | 说明 | 常见取值 |\n",
    "| :--- | :--- | :--- |\n",
    "| `left` | 左侧的DataFrame | (必需) |\n",
    "| `right` | 右侧的DataFrame | (必需) |\n",
    "| `on` | 连接的列名 | 字符串（单列）或列表（多列） |\n",
    "| `how` | **连接类型** | `'inner'`（默认）, `'left'`, `'right'`, `'outer'`, `'cross'` |\n",
    "| `left_on`/`right_on` | 指定左右表不同的连接键 | 列名字符串或列表 |\n",
    "| `left_index`/`right_index` | 是否使用索引作为连接键 | `True`/`False` |\n",
    "| `suffixes` | 重复列名的后缀 | 元组形式，如 `('_left', '_right')` |\n",
    "| `indicator` | 是否显示行来源 | `True`/`False` 或字符串（指定列名） |\n",
    "| `validate` | 验证合并关系 | `'one_to_one'`, `'one_to_many'`, `'many_to_one'`, `'many_to_many'` |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 18 - <font color = '#1B85FF' >merge</font>｜按单键\n",
    "\n",
    "根据 `key` 连接 `left` 和 `right`\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_on_key.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],\n",
    "                     'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "\n",
    "right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],\n",
    "                      'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D1', 'D2', 'D3']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "key",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "df36a129-1175-4464-a64b-85c8c3b904b2",
       "rows": [
        [
         "0",
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "K1",
         "A1",
         "B1",
         "C1",
         "D1"
        ],
        [
         "2",
         "K2",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "3",
         "K3",
         "A3",
         "B3",
         "C3",
         "D3"
        ]
       ],
       "shape": {
        "columns": 5,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K2</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K3</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key   A   B   C   D\n",
       "0  K0  A0  B0  C0  D0\n",
       "1  K1  A1  B1  C1  D1\n",
       "2  K2  A2  B2  C2  D2\n",
       "3  K3  A3  B3  C3  D3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 19 - <font color = '#1B85FF' >merge</font>｜按多键\n",
    "\n",
    "根据 `key1` 和 `key2` 连接 `left` 和 `right`\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_on_key_multiple.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "\n",
    "\n",
    "right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D1', 'D2', 'D3']})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "key1",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key2",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "56b03b77-7948-4838-9839-0a96d4fe671f",
       "rows": [
        [
         "0",
         "K0",
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "K1",
         "K0",
         "A2",
         "B2",
         "C1",
         "D1"
        ],
        [
         "2",
         "K1",
         "K0",
         "A2",
         "B2",
         "C2",
         "D2"
        ]
       ],
       "shape": {
        "columns": 6,
        "rows": 3
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2   A   B   C   D\n",
       "0   K0   K0  A0  B0  C0  D0\n",
       "1   K1   K0  A2  B2  C1  D1\n",
       "2   K1   K0  A2  B2  C2  D2"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right,on=['key1','key2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 20 - <font color = '#1B85FF' >merge</font>｜左外连接\n",
    "\n",
    "\n",
    "如下图所示的结果连接 left 和 right，保留左表全部键\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_on_key_left.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "key1",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key2",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "5c028238-bd17-48f5-8017-d98ab233a88e",
       "rows": [
        [
         "0",
         "K0",
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "K0",
         "K1",
         "A1",
         "B1",
         null,
         null
        ],
        [
         "2",
         "K1",
         "K0",
         "A2",
         "B2",
         "C1",
         "D1"
        ],
        [
         "3",
         "K1",
         "K0",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "4",
         "K2",
         "K1",
         "A3",
         "B3",
         null,
         null
        ]
       ],
       "shape": {
        "columns": 6,
        "rows": 5
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K2</td>\n",
       "      <td>K1</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2   A   B    C    D\n",
       "0   K0   K0  A0  B0   C0   D0\n",
       "1   K0   K1  A1  B1  NaN  NaN\n",
       "2   K1   K0  A2  B2   C1   D1\n",
       "3   K1   K0  A2  B2   C2   D2\n",
       "4   K2   K1  A3  B3  NaN  NaN"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right,on=['key1','key2'],how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 21 - <font color = '#1B85FF' >merge</font>｜右外连接\n",
    "\n",
    "\n",
    "如下图所示的结果连接 left 和 right，保留右表全部键\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_on_key_right.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "key1",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key2",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "11bd55a5-a218-43ba-b824-d2716ef6c9e8",
       "rows": [
        [
         "0",
         "K0",
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "K1",
         "K0",
         "A2",
         "B2",
         "C1",
         "D1"
        ],
        [
         "2",
         "K1",
         "K0",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "3",
         "K2",
         "K0",
         null,
         null,
         "C3",
         "D3"
        ]
       ],
       "shape": {
        "columns": 6,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K2</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2    A    B   C   D\n",
       "0   K0   K0   A0   B0  C0  D0\n",
       "1   K1   K0   A2   B2  C1  D1\n",
       "2   K1   K0   A2   B2  C2  D2\n",
       "3   K2   K0  NaN  NaN  C3  D3"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right,on=['key1','key2'],how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 22 - <font color = '#1B85FF' >merge</font>｜全外连接\n",
    "\n",
    "\n",
    "如下图所示的结果连接 left 和 right，保留全部键\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_on_key_outer.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "key1",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key2",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "7837f71d-c068-46f3-a416-37335cd4948b",
       "rows": [
        [
         "0",
         "K0",
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "K0",
         "K1",
         "A1",
         "B1",
         null,
         null
        ],
        [
         "2",
         "K1",
         "K0",
         "A2",
         "B2",
         "C1",
         "D1"
        ],
        [
         "3",
         "K1",
         "K0",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "4",
         "K2",
         "K0",
         null,
         null,
         "C3",
         "D3"
        ],
        [
         "5",
         "K2",
         "K1",
         "A3",
         "B3",
         null,
         null
        ]
       ],
       "shape": {
        "columns": 6,
        "rows": 6
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>K1</td>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K2</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>K2</td>\n",
       "      <td>K1</td>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2    A    B    C    D\n",
       "0   K0   K0   A0   B0   C0   D0\n",
       "1   K0   K1   A1   B1  NaN  NaN\n",
       "2   K1   K0   A2   B2   C1   D1\n",
       "3   K1   K0   A2   B2   C2   D2\n",
       "4   K2   K0  NaN  NaN   C3   D3\n",
       "5   K2   K1   A3   B3  NaN  NaN"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right,on=['key1','key2'],how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 23 - <font color = '#1B85FF' >merge</font>｜内连接\n",
    "\n",
    "\n",
    "如下图所示的结果连接 left 和 right，保留交集\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_on_key_inner.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "key1",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key2",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "3b265f6b-abe6-46f3-855a-ef0bb6c21c49",
       "rows": [
        [
         "0",
         "K0",
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "1",
         "K1",
         "K0",
         "A2",
         "B2",
         "C1",
         "D1"
        ],
        [
         "2",
         "K1",
         "K0",
         "A2",
         "B2",
         "C2",
         "D2"
        ]
       ],
       "shape": {
        "columns": 6,
        "rows": 3
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2   A   B   C   D\n",
       "0   K0   K0  A0  B0  C0  D0\n",
       "1   K1   K0  A2  B2  C1  D1\n",
       "2   K1   K0  A2  B2  C2  D2"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right,on=['key1','key2'],how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 24 - <font color = '#1B85FF' >merge</font>｜重复索引\n",
    "\n",
    "\n",
    "重新产生数据并按下图所示进行连接\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_merge_overlapped_suffix.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'k': ['K0', 'K1', 'K2'], 'v': [1, 2, 3]})\n",
    "\n",
    "right = pd.DataFrame({'k': ['K0', 'K0', 'K3'], 'v': [4, 5, 6]})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "k",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "v_1",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "v_2",
         "rawType": "int64",
         "type": "integer"
        }
       ],
       "ref": "c63e5e49-a1a9-49ae-af28-acee0cb3950d",
       "rows": [
        [
         "0",
         "K0",
         "1",
         "4"
        ],
        [
         "1",
         "K0",
         "1",
         "5"
        ]
       ],
       "shape": {
        "columns": 3,
        "rows": 2
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k</th>\n",
       "      <th>v_1</th>\n",
       "      <th>v_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k  v_1  v_2\n",
       "0  K0    1    4\n",
       "1  K0    1    5"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=left,right=right,on='k',suffixes=['_1','_2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### join - 组合"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最后一个数据合并的常用且重要的操作是`join`\n",
    "\n",
    "同样很难找到比官方文档更好的练习，以下案例来源或基于 [👉官方文档](https://pandas.pydata.org/pandas-docs/version/0.20/merging.html#database-style-dataframe-joining-merging) 中的案例修改而来\n",
    "\n",
    "在练习之前应执行每题下方的代码生成数据\n",
    "\n",
    "并应预览不同数据的结构以及每题的图解（如果有）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`join`方法的核心参数决定了合并的行为，理解它们是灵活运用的关键。\n",
    "\n",
    "| 参数 | 说明 | 常用取值与效果 |\n",
    "| :--- | :--- | :--- |\n",
    "| **`other`** | 要合并的对象。 | `DataFrame`, `Series` (需有名称)，或它们的`列表` (用于一次性合并多个)。 |\n",
    "| **`on`** | 指定左侧DF中用于连接的列（或列列表）。 | 默认为`None`（使用索引）。指定后，左侧以此列，右侧仍用其**索引**进行匹配。 |\n",
    "| **`how`** | 合并方式，决定结果中包含哪些行。 | `'left'`（默认）：保留左侧所有行。<br>`'right'`：保留右侧所有行。<br>`'inner'`：只保留两侧都有的行。<br>`'outer'`：保留两侧的所有行，缺失值用`NaN`填充。<br>`'cross'`：创建两集合的笛卡尔积。 |\n",
    "| **`lsuffix`/`rsuffix`** | 列名冲突时，分别为左、右侧列名添加的后缀。 | 默认为空字符串`''`。当左右DF有同名列且非连接键时，**必须指定**以避免错误。 |\n",
    "| **`sort`** | 是否按连接键对结果进行字典序排序。 | `False`（默认）：不排序，顺序由连接类型决定。<br>`True`：排序。 |\n",
    "| **`validate`**** | 可选，检查合并是否为特定类型，有助于数据质量验证。 | 如 `'1:1'`, `'m:1'`, `'1:m'`, `'m:m'`。例如，设定`validate='m:1'`可检查右侧的键是否唯一。 |\n",
    "\n",
    "**注意**：当`other`参数是DataFrame列表时，`on`, `lsuffix`, `rsuffix`参数不受支持。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`join`与`merge`/`concat`的简单比较\n",
    "\n",
    "了解不同合并方法的区别有助于在适当场景选择最佳工具。\n",
    "\n",
    "| 方法 | 主要适用场景 | 默认行为 | 关键区别 |\n",
    "| :--- | :--- | :--- | :--- |\n",
    "| **`df.join()`** | **沿索引**快速横向合并多个DataFrame。 | 左连接，基于**索引**。 | 语法简洁，索引操作的专用工具。 |\n",
    "| **`pd.merge()`** | 基于**一个或多个列**（像数据库SQL JOIN）进行复杂合并。 | 内连接，基于**列**。 | 功能更强大灵活，支持左右分别指定列等。 |\n",
    "| **`pd.concat()`** | 沿**轴**（行或列）简单堆叠多个DataFrame。 | 外连接，沿**轴0（行）** 拼接。 | 用于轴向拼接，而非基于键的合并。 |\n",
    "\n",
    "简单来说：\n",
    "*   如果合并键都是索引，用`join`通常最简洁。\n",
    "*   如果合并键是列，或者需要更复杂的列匹配（如左右键名不同），用`merge`。\n",
    "*   如果只是简单地将多个数据集堆叠起来（增加行或列），用`concat`。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n",
    "                     'B': ['B0', 'B1', 'B2']},\n",
    "                     index=['K0', 'K1', 'K2'])\n",
    "\n",
    "\n",
    "right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D2', 'D3']},\n",
    "                      index=['K0', 'K2', 'K3'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 25 -  <font color ='#27BE49'>join</font>｜左对齐\n",
    "\n",
    "合并 left 和 right，并按照 left 的索引进行对齐"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_join.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "200a1e1d-26f5-47b1-abe9-b73ea91475b6",
       "rows": [
        [
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "K1",
         "A1",
         "B1",
         null,
         null
        ],
        [
         "K2",
         "A2",
         "B2",
         "C2",
         "D2"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 3
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B    C    D\n",
       "K0  A0  B0   C0   D0\n",
       "K1  A1  B1  NaN  NaN\n",
       "K2  A2  B2   C2   D2"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 26 - <font color ='#27BE49'>join</font>｜左对齐（外连接）\n",
    "\n",
    "按下图所示进行连接\n",
    "\n",
    "思考：merge 做法\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_join_outer.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "f7047ade-27f9-4b70-8777-862e1ff406c3",
       "rows": [
        [
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "K1",
         "A1",
         "B1",
         null,
         null
        ],
        [
         "K2",
         "A2",
         "B2",
         "C2",
         "D2"
        ],
        [
         "K3",
         null,
         null,
         "C3",
         "D3"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A    B    C    D\n",
       "K0   A0   B0   C0   D0\n",
       "K1   A1   B1  NaN  NaN\n",
       "K2   A2   B2   C2   D2\n",
       "K3  NaN  NaN   C3   D3"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# left.join(right,how='outer')\n",
    "pd.merge(left=left,right=right,left_index=True,right_index=True,how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 27 - <font color ='#27BE49'>join</font>｜左对齐（内连接）\n",
    "\n",
    "按下图所示进行连接\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_join_inner.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "5b0e8edc-9e4f-4a4c-a2af-631272065f9f",
       "rows": [
        [
         "K0",
         "A0",
         "B0",
         "C0",
         "D0"
        ],
        [
         "K2",
         "A2",
         "B2",
         "C2",
         "D2"
        ]
       ],
       "shape": {
        "columns": 4,
        "rows": 2
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B   C   D\n",
       "K0  A0  B0  C0  D0\n",
       "K2  A2  B2  C2  D2"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right,how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 28 - <font color ='#27BE49'>join</font>｜按索引\n",
    "\n",
    "重新产生数据并按下图所示进行连接（根据 `key`）\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_join_key_columns.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "                     'key': ['K0', 'K1', 'K0', 'K1']})\n",
    "\n",
    "\n",
    "right = pd.DataFrame({'C': ['C0', 'C1'],\n",
    "                      'D': ['D0', 'D1']},\n",
    "                      index=['K0', 'K1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "string"
        }
       ],
       "ref": "30429e97-9bb9-4705-8f07-fff21865d8e5",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "K0",
         "C0",
         "D0"
        ],
        [
         "2",
         "A2",
         "B2",
         "K0",
         "C0",
         "D0"
        ],
        [
         "1",
         "A1",
         "B1",
         "K1",
         "C1",
         "D1"
        ],
        [
         "3",
         "A3",
         "B3",
         "K1",
         "C1",
         "D1"
        ]
       ],
       "shape": {
        "columns": 5,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>key</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>K1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>K1</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B key   C   D\n",
       "0  A0  B0  K0  C0  D0\n",
       "2  A2  B2  K0  C0  D0\n",
       "1  A1  B1  K1  C1  D1\n",
       "3  A3  B3  K1  C1  D1"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right,on='key',how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 29 - <font color ='#27BE49'>join</font>｜按索引（多个）\n",
    "\n",
    "重新产生数据并按下图所示进行连接（根据 `key1` 和 `key2`）\n",
    "\n",
    "![](https://pandas.pydata.org/pandas-docs/version/0.20/_images/merging_join_multikeys.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n",
    "                     'B': ['B0', 'B1', 'B2', 'B3'],\n",
    "                     'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1']})\n",
    "\n",
    "\n",
    "index = pd.MultiIndex.from_tuples([('K0', 'K0'), ('K1', 'K0'),\n",
    "                                  ('K2', 'K0'), ('K2', 'K1')])\n",
    "\n",
    "\n",
    "right = pd.DataFrame({'C': ['C0', 'C1', 'C2', 'C3'],\n",
    "                   'D': ['D0', 'D1', 'D2', 'D3']},\n",
    "                  index=index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.microsoft.datawrangler.viewer.v0+json": {
       "columns": [
        {
         "name": "index",
         "rawType": "int64",
         "type": "integer"
        },
        {
         "name": "A",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "B",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key1",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "key2",
         "rawType": "object",
         "type": "string"
        },
        {
         "name": "C",
         "rawType": "object",
         "type": "unknown"
        },
        {
         "name": "D",
         "rawType": "object",
         "type": "unknown"
        }
       ],
       "ref": "53606887-f657-400f-91a4-10a777b0a250",
       "rows": [
        [
         "0",
         "A0",
         "B0",
         "K0",
         "K0",
         "C0",
         "D0"
        ],
        [
         "1",
         "A1",
         "B1",
         "K0",
         "K1",
         null,
         null
        ],
        [
         "2",
         "A2",
         "B2",
         "K1",
         "K0",
         "C1",
         "D1"
        ],
        [
         "3",
         "A3",
         "B3",
         "K2",
         "K1",
         "C3",
         "D3"
        ]
       ],
       "shape": {
        "columns": 6,
        "rows": 4
       }
      },
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>K0</td>\n",
       "      <td>K1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>C1</td>\n",
       "      <td>D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A3</td>\n",
       "      <td>B3</td>\n",
       "      <td>K2</td>\n",
       "      <td>K1</td>\n",
       "      <td>C3</td>\n",
       "      <td>D3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B key1 key2    C    D\n",
       "0  A0  B0   K0   K0   C0   D0\n",
       "1  A1  B1   K0   K1  NaN  NaN\n",
       "2  A2  B2   K1   K0   C1   D1\n",
       "3  A3  B3   K2   K1   C3   D3"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left.join(right,on=['key1','key2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](http://liuzaoqi.oss-cn-beijing.aliyuncs.com/2021/09/16/16317972442543.jpg?域名/sample.jpg?x-oss-process=style/stylename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "384px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
